I have this awful field that I managed to get what I want using Substring. However, I am unable to remove the leading space when there is one.
This is what I am using:
Case When Left(ah.TokenString,2)='21' AND ah.TransactionType='DPA' THEN LTRIM(SUBSTRING(ah.TokenString,27,40))
Thanks for your help
It may be that it is not a space character, but some other not-printable character. You can convert to binary and see what it is.
SELECT CAST(SUBSTRING(ah.TokenString,27,40) AS VARCHAR(MAX))
If it is a space, you should see something like 0x20....
But, if it is a tab, for example, you would see 0x09...
You can use REPLACE to remove those characters once you know what they are.
Thank you for your response.
Using your recommendation to identify the character, it reflected exactly what I have in my Case statement. I didn't see any character info.
I tried to replace the tab. However, it's not a tab either and still reflecting an empty spot.
Case When Left(ah.TokenString,2)='21' AND ah.TransactionType='DPA' THEN REPLACE(SUBSTRING(ah.TokenString,27,40),CHAR(9),'')
What other type of Control Character could it be?
How did you check this?
One way I do it is by copying the data into notepad++
Ugh! I mistyped. You should use
SELECT CAST(SUBSTRING(ah.TokenString,27,40) AS VARBINARY(MAX))
VARBINARY, not VARCHAR!! Sorry about that.
In my sample all are 0x09.... with two exceptions ... once has 0x4.... and one has 0x5....
You can then replace the expression
Assuming, of course, that you don't care about 0x09 and 0x04 anywhere else in the string as well.
I think that did the trick. Thank you so much.
Until tomorrow a new odd string 0x666 shows up. @Moxioron you need to find the underlying issue that causes the data to be like that