LTRIM Not Removing leading space

1

Hello.

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

2

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.

3

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?

4

How did you check this?
One way I do it is by copying the data into notepad++

5

Ugh! I mistyped. You should use

SELECT CAST(SUBSTRING(ah.TokenString,27,40) AS VARBINARY(MAX))

VARBINARY, not VARCHAR!! Sorry about that.

6

Thank you.

In my sample all are 0x09.... with two exceptions ... once has 0x4.... and one has 0x5....

7

You can then replace the expression

LTRIM(SUBSTRING(ah.TokenString,27,40))

with

REPLACE(REPLACE(LTRIM(SUBSTRING(ah.TokenString,27,40)),CHAR(0x09),''), CHAR(0x04),'')

Assuming, of course, that you don't care about 0x09 and 0x04 anywhere else in the string as well.

8

I think that did the trick. Thank you so much.

9

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