SQLTeam.com | Weblogs | Forums

2 Replace Functions in 1 column


#1

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.


#2

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

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


#3

Thanks! that's spot on.

Cheers

David.


#4

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?


#5

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 ?


#6

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


#7

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.