replace to 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 2005 Forums

Transact-SQL (2005)

replace to null

Author

Topic

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-07-09 : 20:40:50

i want to replace METRO to be Y, at the same time, if the value is RURAL, i want to set/replace it to be NULL

i used:
select replace(replace('METRO','METRO','Y'),'RURAL',null)

it didn't work

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-09 : 21:36:36

[code]SELECT word, new = REPLACE(NULLIF(word, 'RURAL'), 'METRO', 'Y')
FROM (
SELECT word = 'METRO' UNION ALL
SELECT word = 'RURAL'
) w
/*
word new
----- ----
METRO Y
RURAL NULL
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2007-07-09 : 21:44:14

works like a charm thanx :)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-10 : 05:07:03

FYI, Tan's is short form of


SELECT	word,
	case 
		when word='METRO' then 'Y' 
		when word='RURAL' then null 
		else word 
	end as new
FROM	(
		SELECT	word = 'METRO'	UNION ALL
		SELECT	word = 'RURAL'
	) w

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources