SQL Server - CAST AND DIVIDE

Posted by rs on Stack Overflow See other posts from Stack Overflow or by rs
Published on 2010-03-11T20:12:45Z Indexed on 2010/03/11 20:14 UTC
Read the original article Hit count: 315

Filed under:
|
|
|
|
DECLARE @table table(XYZ VARCHAR(8) , id int)

INSERT INTO @table
SELECT '4000', 1
UNION ALL
SELECT '3.123', 2
UNION ALL
SELECT '7.0', 3
UNION ALL
SELECT '80000', 4
UNION ALL
SELECT NULL, 5

SELECT
CASE 
WHEN PATINDEX('^[0-9]{1,5}[\.][0-9]{1,3}$', XYZ) = 0 THEN XYZ
WHEN PATINDEX('^[0-9]{1,8}$',XYZ) = 0 THEN CAST(XYZ AS decimal(18,3))/1000
ELSE NULL END
FROM @table

This part - CAST(XYZ AS decimal(18,3))/1000 doesn't divide value it gives me more number of zeros after decimal instead of dividing it. (I even enclosed that in brackets and tried but same result) Am i doing something wrong here?

© Stack Overflow or respective owner

Related posts about sql-server

Related posts about sql