SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Nested Replace returning NULL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Zath
Constraint Violating Yak Guru

USA
286 Posts

Posted - 12/05/2013 :  14:15:32  Show Profile  Visit Zath's Homepage  Reply with Quote
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

United Kingdom
22431 Posts

Posted - 12/05/2013 :  14:38:30  Show Profile  Reply with Quote
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()
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 12/05/2013 :  14:39:06  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000