-2

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?

Thom A
  • 88,727
  • 11
  • 45
  • 75
gene
  • 2,098
  • 7
  • 40
  • 98
  • 1
    It could be an invisible character that is not a white space, which is why LTRIM cannot remove it. Can you try getting the character code (ASCII/UNICODE) of that character? It might give you a lead to follow. – Ram RS Mar 25 '20 at 18:34
  • The example you have given us *does* have it removed [db<>fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=a2f4958d85927f53701c9def0662cb66). ACutally copy the value you receive, and show is your SQL with LTRIM – Thom A Mar 25 '20 at 18:41
  • @Larnu That's because I typed it in here. – gene Mar 25 '20 at 18:45
  • That's why I said *". ACutally copy the value you receive, and show is your SQL with LTRIM"* @gene . Your question doesn't replicate the problem, nor does it use `LTRIM`. – Thom A Mar 25 '20 at 18:46
  • @Larnu I modified the question – gene Mar 25 '20 at 18:50
  • The value is `'" T000000000004"'`. `LTRIM` only strips leading `' '` characters. `"` is the first character, so no whitespace is removed. – Thom A Mar 25 '20 at 18:52
  • @Larnu Actually the string is T000000000004 without quotes. I put quotes for an example when was concatenating it before I changed the descriptcription of the problem – gene Mar 25 '20 at 18:54
  • Again, that doesn't replciate the problem [db<.fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=d0a328eb2efc4bdf02a25f7242255559). – Thom A Mar 25 '20 at 18:57
  • @Larnu I cannot replicate the problem here. I'm explaining what is the problem. As I said there is some kind of character before the data and when I trim it, it does not get trimmed. How can I replicate it here if the character is interpreted differently then on my PC – gene Mar 25 '20 at 19:04
  • If there *were* some kind of other character, then simply copy and pasting it into the question would copy it. The fact that when you copy it doesn't replicate it means that there's something you aren't telling us. – Thom A Mar 25 '20 at 19:05
  • @Larnu I just copied the data as I got it from the select statement – gene Mar 25 '20 at 19:07
  • @gene with the greatest respect, then the value you put in the post is not a copy, as it has the `varbinary` value `0x20202054303030303030303030303034` – Thom A Mar 25 '20 at 19:11
  • FWIW: An example of trimming all .NET whitespace characters is [here](https://stackoverflow.com/questions/35245812/whats-a-good-way-to-trim-all-whitespace-characters-from-a-string-in-t-sql-witho/35247507#35247507). And `0x0A` is a linefeed character. – HABO Mar 25 '20 at 19:11
  • @Larnu When I do `select convert(varbanary,data) from mytable` I have exactly what I posted in my description – gene Mar 25 '20 at 19:16
  • Yes, but when ***we*** copy the data you supplied and do a `CONVERT` we don't get the same value... – Thom A Mar 25 '20 at 19:17

1 Answers1

2

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])
Thom A
  • 88,727
  • 11
  • 45
  • 75