C Layne C Layne - 2 months ago 21
SQL Question

VARCHAR Column contains both decimal or Money

I have a varchar column that can either be a money or decimal data type. Sample values stored are 500 & .02 for each example.

How can I test to see if it's Money or a decimal and if it is a decimal add a leading 0 in front of it so the result will be 0.02?

Thanks in advance

Answer

Here is how you could convert all of the data to be consistently displayed as the Money datatype.

DECLARE @BadDesign TABLE (
    BadDataTyping VARCHAR(100)
)

INSERT INTO @BadDesign (BadDataTyping) VALUES ('500')
INSERT INTO @BadDesign (BadDataTyping) VALUES ('.02')

SELECT * FROM @BadDesign
SELECT CONVERT(MONEY,BadDataTyping) FROM @BadDesign

UPDATE @BadDesign
SET BadDataTyping=CONVERT(MONEY,BadDataTyping)

SELECT * FROM @BadDesign