CODEWITHSUNDEEP
sqlsql-servert-sql
neeko
neeko

Reputation: 2000

CASE WHEN LEN after decimal point is 1 add 0

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

Upvotes: 0

Views: 211

Answers (3)

GarethD
GarethD

Reputation: 69789

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

Upvotes: 2

Punter015
Punter015

Reputation: 1796

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

Upvotes: 1

dougajmcdonald
dougajmcdonald

Reputation: 20047

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

Upvotes: 3

Related Questions