C Layne C Layne - 1 year ago 73
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 Source

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

    BadDataTyping VARCHAR(100)

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

SELECT * FROM @BadDesign

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

SELECT * FROM @BadDesign