SQL converting Text fields

Question

I'm bumping into somenull fields in a SQL2005 db.

Some report Null and others have values like 1.00 or 713.00.
I'd like a bullet proof way to convert the 'Null's to 0 and the '1.00' and '713.00' values into Money types.

Answer 1

You can convert from null via the coalesce function:

coalesce(a, b) //returns b if a is null, a otherwise

Answer 2

To cast from text to money and handle nulls, try this:

CAST(COALESCE(column, '0') AS MONEY)

See COALESCE and CAST for details.

An alternative when things are more complex is CASE..WHEN.

Answer 3

I'm not sure what the performance will be like, but 2005 introduced the varchar(MAX) and nvarchar(MAX) data types (among others like varbinary(MAX). These are true varchars and nvarchars, and can be used in any place where they could (like Convert), but also have the capacity of Text and Image fields. In fact, the Text and Image types are actually deprecated, so going forward it's recommended that you use the new types.

That being said, you could just use this:

convert(Money, coalesce(text_column_name, 0))

Answer 4

CREATE TABLE dbo.Test_Text_Convert
(
     my_string     TEXT     NULL
)
GO
INSERT INTO dbo.Test_Text_Convert VALUES (NULL)
INSERT INTO dbo.Test_Text_Convert VALUES ('7.10')
INSERT INTO dbo.Test_Text_Convert VALUES ('xxx')
INSERT INTO dbo.Test_Text_Convert VALUES ('$20.20')
INSERT INTO dbo.Test_Text_Convert VALUES ('20.2020')
GO

SELECT
     CASE
          WHEN ISNUMERIC(CAST(my_string AS VARCHAR(MAX))) = 1
               THEN CAST(ISNULL(CAST(my_string AS VARCHAR(MAX)), '0') AS MONEY)
          ELSE 0
     END
FROM
    dbo.Test_Text_Convert
GO

I've set invalid strings to be 0, but you could easily change that behavior.