SQLTeam.com | Weblogs | Forums

2 Replace Functions in 1 column

Hello all,

So in a table I have the following

94 0001 1000 100
93 0002 0002 300
95 0006 0000 100
000100

etc...

1st off I need to get rid of the spaces, so I did that with a replace function:-

,replace (AS_GWSL_INVE_DATA.CONTROLCHARGECODE,' ','') AS [Control Charge Code]

however what I then want to do is any values that = 000100 I want to make a null

So I then wrote a replace function with 2 replace in it:-

,replace (replace (AS_GWSL_INVE_DATA.CONTROLCHARGECODE,' ',''),'000100','') AS [Control Charge Code]

however doing that also nulls values that I want such as 95 0006 0000 100 as it sees that value 000100 and nulls it.

Is there a way I can say any value less than 13 characters set to null and then remove spaces from all the remaining.

thanks in advance

David.

Remove the spaces after removing the 000100 by reversing the order of REPLACE functions.

replace (replace (AS_GWSL_INVE_DATA.CONTROLCHARGECODE,'000100',''),' ','')

1 Like

Thanks! that's spot on.

Cheers

David.

ah no just checked in my report in SSRS and now the other values are coming back as 9400011 for instance and 9100020 etc...

Can you help?

You do want that in the result, don't you? When you replace all the space sin the first line in your original post which is this:

94 0001 1000 100

you get 940001...

What is the output you want to get given the example you posted ?

Hi dijeon,

I just tried this code. Hope it helps you.

Declare @t table ( i varchar(20))

Insert into @t values ('94 0001 1000 100')
Insert into @t values ('93 0002 0002 300')
Insert into @t values('95 0006 0000 100')
Insert into @t values('000100')

Select * from @t

Select
case when REPLACE(i,' ','') = '000100' then 'NULL'
else REPLACE(i,' ','')
end
from @t

regards
Anna

Hi thanks for this, however the only issue is that new values can get added to that list, so is there any way of that table being dynamically generated?

Thanks.

David.