CASE WHEN LEN after decimal point is 1 add 0

Question

I have some data like this in the database as varchar

when the amount of characters after the decimal point is 1 I would like to add a 0

4.2
4.80
2.43
2.45

becomes

4.20
4.80
2.43
2.45

Any ideas? I am currently trying to play around with LEFT and LEN but can't get it to work

dougajmcdonald · Answer 1 · 2014-01-29T13:46:58.803

3

You can convert you varchars to decimals if you want, but it could be unsafe as you don't know if it will always work:

CONVERT(decimal(10,2), MyColumn)

That kind of thing.

It should be noted that the precision value (in the example above, the '2') will trim values off if your varchars were say 4.103.

Additionally, I notice that if you're using SQL Server 2012, that there is now 'TRY_CONVERT' and 'TRY_CAST' which would be safer:

http://msdn.microsoft.com/en-us/library/hh230993.aspx

edited Jan 29 '14 at 13:46

answered Jan 29 '14 at 13:40

dougajmcdonald

19,231
12
56
89

thanks for your reply, I've tried to do this but it just says `error when converting varchar to numeric` I have checked using ISNUMERIC and all the values are numeric :/ any idea? – neeko Jan 29 '14 at 13:49
If that's the result of the query then you do have something in there which SQL can't convert. Have a look at some of the options here for ideas on how to guard against that kind of situation: http://stackoverflow.com/questions/14153665/sql-server-error-converting-data-type-varchar-to-numeric – dougajmcdonald Jan 29 '14 at 13:55
Can you post your entire query to have a look? – dougajmcdonald Jan 29 '14 at 13:56

score 2 · Accepted Answer · edited Jul 29 '20 at 18:28

You could just cast it to the appropriate data type? (Or better still, store it as the appropriate data type):

SELECT  v,
        AsDecimal = CAST(v AS DECIMAL(3, 2))
FROM    (VALUES ('4.2'), ('4.80'), ('2.43'), ('2.45')) t (v)

Will give:

v       AsDecimal
4.2     4.20
4.80    4.80
2.43    2.43
2.45    2.45

If this is not an option you can use:

SELECT  v,
        AsDecimal = CAST(v AS DECIMAL(4, 2)),
        AsVarchar = CASE WHEN CHARINDEX('.', v) = 0 THEN v + '.00'
                        WHEN CHARINDEX('.', REVERSE(v)) > 3 THEN SUBSTRING(v, 1, CHARINDEX('.', v) + 2)
                        ELSE v + REPLICATE('0', 3 - CHARINDEX('.', REVERSE(v)))
                    END
FROM    (VALUES ('4.2'), ('4.80'), ('2.43'), ('2.45'), ('54'), ('4.001'), ('35.051')) t (v);

Which gives:

v       AsDecimal   AsVarchar
4.2     4.20        4.20
4.80    4.80        4.80
2.43    2.43        2.43
2.45    2.45        2.45
54      54.00       54.00
4.001   4.00        4.00
35.051  35.05       35.05

Finally, if you have non varchar values you need to check the conversion first with ISNUMERIC, but this has its flaws:

SELECT  v,
        AsDecimal = CASE WHEN ISNUMERIC(v) = 1 THEN CAST(v AS DECIMAL(4, 2)) END,
        AsVarchar = CASE WHEN ISNUMERIC(v) = 0 THEN v
                        WHEN CHARINDEX('.', v) = 0 THEN v + '.00'
                        WHEN CHARINDEX('.', REVERSE(v)) > 3 THEN SUBSTRING(v, 1, CHARINDEX('.', v) + 2)
                        ELSE v + REPLICATE('0', 3 - CHARINDEX('.', REVERSE(v)))
                    END,
        SQLServer2012 = TRY_CONVERT(DECIMAL(4, 2), v)
FROM    (VALUES ('4.2'), ('4.80'), ('2.43'), ('2.45'), ('54'), ('4.001'), ('35.051'), ('fail')) t (v);

Which gives:

v       AsDecimal   AsVarchar   SQLServer2012
4.2     4.20        4.20        4.20
4.80    4.80        4.80        4.80
2.43    2.43        2.43        2.43
2.45    2.45        2.45        2.45
54      54.00       54.00       54.00
4.001   4.00        4.00        4.00
35.051  35.05       35.05       35.05
fail    NULL        fail        NULL

this is amazing, thank you so much! I run validation to check that it ISNUMERIC before it gets in inserted so hopefully they should all be valid — neeko, Jan 29 '14 at 13:59

score 1 · Answer 3 · answered Jan 29 '14 at 13:49

1

CREATE TABLE [dbo].[test10](
[number] [nvarchar](10) NULL
)

--Insert the values.

Just an addition to the above answer by @dougajmcdonald

select convert(decimal(10,2),number) as Added0 from test10 where ISNUMERIC(number) <> 0

answered Jan 29 '14 at 13:49

Punter015

1,718
10
13

CASE WHEN LEN after decimal point is 1 add 0

3 Answers3