I load data from a text file and it seems that it does not contain leading space, however when I SELECT from a table, I see the leading space but cannot remove it with a LTRIM function:
SELECT ltrim(DATA) FROM MYTABLE WHERE LineNumber = 4
I'm getting the following:
T000000000004
with a single leading space before T
When I do select convert(varbinary,data) from mytable, that's what I get:
0x0A54303030303030303030303034
In the file it looks ok: T000000000004 - no leading space and it starts from the first character in the file. However, in the table it's inserted with a leading space.
How can I fix it?
As HABO mentioned, your value doesn't start with a space, it doesn't actually have any white space in it at all, it has a leading Line Feed (character 10, or 0X0A).
To remove these, and any carriage returns you might have too, you can use REPLACE:
REPLACE(REPLACE(data,CHAR(10),'')),CHAR(13),'')
(L/R)TRIM only remove leading/trailing white space. Nothing else.
If there could be a range of leading characters, and you want to remove all of them up to say the first alphanumerical character, you can use PATINDEX and STUFF:
SELECT STUFF(V.[data],1,PATINDEX('%[A-z1-9]%',V.[data])-1,'')
FROM (VALUES(CHAR(10) + CHAR(13) + ' -T000000129B'))V([data])