djieon
March 17, 2017, 5:32pm
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.
JamesK
March 17, 2017, 5:49pm
2
Remove the spaces after removing the 000100 by reversing the order of REPLACE functions.
replace (replace (AS_GWSL_INVE_DATA.CONTROLCHARGECODE,'000100',''),' ','')
1 Like
djieon
March 17, 2017, 6:03pm
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?
JamesK
March 17, 2017, 6:41pm
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 ?
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
djieon
March 27, 2017, 7:49am
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.