SQL Replace Function

[wileyj]

I am trying to use the REPLACE function to replace a string. The only problem is, I want it to replace any string besides a certain few.

So I want it to replace anything that is not 'AB', 'AC', or empty string ''. The column can contain many different strings, hardcoding each variation is not feasible, example "REPLACE(Field, 'DD', '')". I'd have to do this for each different variation I find, and I'd rather not do this. Is there a way to have the REPLACE function replace all occurrences not matching "IN ('AB', 'AC', '')"?

Hopefully that makes sense.

Thanks!

[NeedSomeAnswers]

The replace function is a VB function not SQL !!

,anyway replace doesn't work like that you can only replace one item in the string at a time.

If you have to do the replace for a number of different Fields you could just loop through them. but if it is the same field and you want to do several replaces on it, then as far as I am aware you would have to write the Replace code for each one.

[wileyj]

Thanks for the response. REPLACE is also a SQL function. I'm in a query window so I know it is, I'm not even touching the VB side of things. This question is purely SQL.

Would there be another SQL function that would accomplish what I'm after?

Thanks

[si_the_geek]

It is not a standard SQL function, it is specific to whatever implementation of SQL you are using - which is why people usually ask (as does the "Before you post" thread at the top of this forum) for you to always tell us which database system you are using.

I'm also a bit unclear on what you are trying to achieve.. do you mean that if the field value is 'AB' or 'AC' then leave it alone, otherwise change it to '' ?

[wild_bill]

Can you give us a sample of your data?

[wileyj]

Sorry, I am using SQL Server 2005, forgot to include that.

Basically what I want is to replace anything that isn't 'AB', 'AC', or '' to an empty string ''. I am wanting to ignore all other values.

[wileyj]

So, in essence, I want this.

REPLACE(Field, NOT IN ('AB', 'AC', ''), '')

[wileyj]

Just to clear up my response, I do not want to ignore them, I want to replace them with ''. This is required so the join will return the expected results. I'm confusing myself now.

[wild_bill]

You could use an update to do this:

declare @table table(field char(2))

insert into @table(field) values ('')
insert into @table(field) values ('AB')
insert into @table(field) values ('AC')
insert into @table(field) values ('AD')
insert into @table(field) values ('DD')

update @table
set field = ''
where field not in ('AB','AC','')

select * from @table

[wild_bill]

Or, a case statement:

declare @table table(field char(2))

insert into @table(field) values ('')
insert into @table(field) values ('AB')
insert into @table(field) values ('AC')
insert into @table(field) values ('AD')
insert into @table(field) values ('DD')

select 
	case
		when field = 'AB' then 'AB'
		when field = 'AC' then 'AC'
		else ''
	end
from @table