nullif - 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 2000 Forums

Transact-SQL (2000)

nullif

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2008-02-11 : 07:28:17

Hi, I am a bit confused about what this does.
What is the difference between the following two queries?

select accountno, SUBSTRING(Contact, 1, CHARINDEX(' ', Contact) ) AS [First Name]
from contact1
order by contact desc

and

select accountno, SUBSTRING(Contact, 1, NULLIF(CHARINDEX(' ', Contact) - 1, -1)) AS [First Name]
from contact1
order by contact desc

Either one seems to give me the same results

thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-11 : 07:31:50

The difference will come into the picture when CONTACT column will not contain any space character.

Try this code and you will notice the difference:

declare @t table
(
	a varchar(10)
)

insert @t
select 'xyz pqr' union all
select 'abc pqr' union all
select 'mnl pqr' union all
select 'xyz'

select substring(a, 1, CHARINDEX(' ', a)) from @t

select SUBSTRING(a, 1, NULLIF(CHARINDEX(' ', a) - 1, -1)) from @t

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-11 : 07:35:07

[code]select accountno,
SUBSTRING(Contact, 1, case CHARINDEX(' ', Contact) when 0 then len(contact) else CHARINDEX(' ', Contact) end ) AS [First Name],
substring(Contact, 1, isnull(nullif(CHARINDEX(' ', Contact), 0), len(Contact)))
from contact1
order by contact desc[/code]

E 12°55'05.25"
N 56°04'39.16"

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2008-02-11 : 07:52:20

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources