May 15, 2013 at 7:47 am
Ive been given a data set to work with with a column for money.
This column is basically a mess but its a varchar value.
Some of the examples are 12,312.00, 17,160.96, 21,519.00
there are lots of issues, thre are even spaces in there which need to be trimmed
Im trying to convert this to a decimal value that I can work with for further calculations. Ive tried
SELECT convert(decimal(19,4),NULLIF(REPLACE(LTRIM(RTRIM(Full_Time_Equivalent_Salary)),',',''),0))
Bu I get the error message
Conversion failed when converting the varchar value '999.96' to data type int.
Ive tried a few decimal types but Im having no joy at all. If anyone has any idea how to do this I would be very grateful
Debbie
May 15, 2013 at 8:06 am
Sorte it.... Its SELECT CAST(REPLACE(LTRIM(RTRIM(Full_Time_Equivalent_Salary)),',','') as decimal(10,4))
May 15, 2013 at 8:17 am
It's more about the order of operations than the format of the string. You must first convert the string to a decimal type before you put it into the NULLIF() function.
SELECT NULLIF(convert(money, Full_Time_Equivalent_Salary),0)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply