Nested Replace returning NULL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Nested Replace returning NULL

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2013-12-05 : 14:15:32

Not sure why this returns NULL for all values:


DECLARE @temp TABLE (Problem16 VARCHAR(40))
INSERT INTO @temp VALUES('$6867.36')
INSERT INTO @temp VALUES('PENDING')

SELECT CAST(REPLACE(REPLACE(Problem16,'$',''), 'PENDING', NULL) as DECIMAL(10,2))
FROM @temp

If each REPLACE is run separately, it returns the proper value.

The main problem is - especially when inheriting a db -
that the Problem16 column is an NVARCHAR(40) type.

There are numbers there representing money, and the word PENDING and Null values.

The ultimate goal is to add in the where clause to bring back the rows that are <= some value....


  WHERE  ISNULL(CAST(REPLACE(REPLACE(Problem16,'$',''), 'PENDING', NULL) as decimal(10,2)), '0') <= 5000

Any suggestions are appreciated.

Thanks!

Kristen
Test

22859 Posts

Posted - 2013-12-05 : 14:38:30

You cannot do

REPLACE(MyColumn, 'SomeString', NULL)

the final parameter has to be a string.

If PENDING is the exact value then this would work


SELECT	CAST(
		REPLACE(
			Nullif(Problem16, 'PENDING')
			,'$','')
	as DECIMAL(10,2))
FROM @temp

If there are several other values that you need to NULLify then I suspect this would be more efficient


SELECT	CASE 
		WHEN Problem16 = 'PENDING' THEN NULL
		WHEN Problem16 LIKE '%GOOFY%' THEN NULL
		ELSE CAST(REPLACE(Problem16, '$','') as DECIMAL(10,2))
		END
FROM @temp

lastly your IsNull() in your WHERE clause is doing:


WHERE IsNull(SomeDecimal_10_2, SomeString)

IsNull() has some strange behaviours when you mix datatypes (for that reason we always use COALESCE() instead of IsNull() - added benefit is that COALESCE() can take 2, or more, parameters - and returns the first one that is non-NULL)

At the least do


WHERE IsNull(... Your Decimal Expression ..., 0.0)

so that the types match. But personally I would still use COALESCE() instead of IsNull()

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-12-05 : 14:39:06

From BOL: REPLACE Returns NULL if any one of the arguments is NULL.

So, you need to do something different to NULL the values out, like a CASE expression.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources