So in a table I have the following
94 0001 1000 100
93 0002 0002 300
95 0006 0000 100
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