LTRIM and RTRIM not working

Question

LTRIM and RTRIM not working

GBeezy

SSCarpal Tunnel

Points: 4717
More actions
May 28, 2014 at 8:22 am

#308562

All,
I have a field in AX that is NVARCHAR(30). Sometimes the value has 5 extra characters at the end and sometimes, it does not. I have been attempting to get rid of the trailing spaces and nothing has worked. What I have tried:
1. LTRIM(RTRIM(colA)) AS ColA
2. RTRIM(LTRIM(colA)) AS ColA
3. RTRIM(colA) AS ColA
4. REPLACE(colA), ' ','') AS ColA (my first argument for replace has 5 spaces)
5. REPLACE(colA), ' ','') AS ColA
6. REPLACE(LTRIM(RTRIM(colA))), ' ', '') AS ColA
7. REPLACE(LTRIM(RTRIM(colA))), ' ', '') AS ColA
8. I tried casting it to a VARCHAR and attempting all of the above and that is not working.
I am not sure why this will not work, but if anyone can provide some insight, I would greatly appreciate it.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

GBeezy (5/28/2014)
All,
I have a field in AX that is NVARCHAR(30). Sometimes the value has 5 extra characters at the end and sometimes, it does not. I have been attempting to get rid of the trailing spaces and nothing has worked. What I have tried:
1. LTRIM(RTRIM(colA)) AS ColA
2. RTRIM(LTRIM(colA)) AS ColA
3. RTRIM(colA) AS ColA
4. REPLACE(colA), ' ','') AS ColA (my first argument for replace has 5 spaces)
5. REPLACE(colA), ' ','') AS ColA
6. REPLACE(LTRIM(RTRIM(colA))), ' ', '') AS ColA
7. REPLACE(LTRIM(RTRIM(colA))), ' ', '') AS ColA
8. I tried casting it to a VARCHAR and attempting all of the above and that is not working.
I am not sure why this will not work, but if anyone can provide some insight, I would greatly appreciate it.

My guess is they are not spaces some other non-printable character. Find on of the offending rows and try this.

select ASCII(right(ColA, 1))

Where ColA like 'SomeKnownValue%'

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

eccentricDBA SSCarpal Tunnel Points: 4844 More actions · Answer 2

Try this to determine what the end character is.

SELECT ASCII(RIGHT(<your column>,1)) FROM <your table> WHERE <some selection criteria>

GBeezy SSCarpal Tunnel Points: 4717 More actions · Answer 3

Gentleman,

I have done this and some rows return a 32 and return a 49. I do apologize as I am not sure what this is telling me, if I should include this in the code, or what I should do with it.

Sean Lange SSC Guru Points: 286573 More actions · Answer 4

GBeezy (5/28/2014)
Gentleman,
I have done this and some rows return a 32 and return a 49. I do apologize as I am not sure what this is telling me, if I should include this in the code, or what I should do with it.

You are looking for nonprintable characters. You might want to look at an ascii chart. 32 = space and 49 = 1. The most likely culprits are going to be 13 and/or 10 which are carriage return / line feed respectively.

There other characters are what is causing the behavior you are seeing. The LTRIM and RTRIM functions are working perfectly fine, it is your data that is at fault here. Once you figure out what characters are there you can handle them accordingly.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

GBeezy (5/28/2014)
All,
I have a field in AX that is NVARCHAR(30). Sometimes the value has 5 extra characters at the end and sometimes, it does not. I have been attempting to get rid of the trailing spaces and nothing has worked. What I have tried:
1. LTRIM(RTRIM(colA)) AS ColA
2. RTRIM(LTRIM(colA)) AS ColA
3. RTRIM(colA) AS ColA
4. REPLACE(colA), ' ','') AS ColA (my first argument for replace has 5 spaces)
5. REPLACE(colA), ' ','') AS ColA
6. REPLACE(LTRIM(RTRIM(colA))), ' ', '') AS ColA
7. REPLACE(LTRIM(RTRIM(colA))), ' ', '') AS ColA
8. I tried casting it to a VARCHAR and attempting all of the above and that is not working.
I am not sure why this will not work, but if anyone can provide some insight, I would greatly appreciate it.

Just an FYI:

The SET ANSI_PADDING setting does not affect the nchar, nvarchar, ntext, text, image, and large value. They always display the SET ANSI_PADDING ON behavior. This means trailing spaces and zeros are not trimmed.

GBeezy SSCarpal Tunnel Points: 4717 More actions · Answer 6

Ok...

So my field earlier had two ASCII characters in them and I was able to use the REPLACE() function to get rid of them.

I have another field that has a bunch of different ASCII characters in them. Using this link, I can see what each one is:

http://www.asciitable.com/

I then Googled, "How to get rid of ASCII characters TSQL" and get a bunch of hits back. I am currently trying to use this function here:

http://www.sqlservercentral.com/Forums/Topic1001736-391-1.aspx

And it doesnt work, unless I am calling/using it wrong, I am passing my code in as the following:

SELECT dbo.RemoveNonASCII(t.OffensiveColumn) AS RemovedASCII

FROM table t

I'm not sure if I am doing something wrong, or if there is a better way to get rid of a variety of ASCII characters in a single column???

Any feedback will be greatly appreciated!!

Thanks Again...

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

GBeezy (5/29/2014)
And it doesnt work, unless I am calling/using it wrong, I am passing my code in as the following:
SELECT dbo.RemoveNonASCII(t.OffensiveColumn) AS RemovedASCII
FROM table t

Do you get an error or is [RemovedASCII] always equal to t.OffensiveColumn?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

GBeezy SSCarpal Tunnel Points: 4717 More actions · Answer 8

I am not getting an error...

Before the function call I using the ASCII(RIGHT(t.OffensiveColumn,1)) to see what the ASCII character is, then calling the function, and returning the results to a temp table. Then on my temp table, the column that the function was on, I am running the ASCII(RIGHT(r.RemovedASCII) and the values values are the same.

Starting ASCII value 68 - ASCII values after function call is 68.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

GBeezy (5/29/2014)
I am not getting an error...
Before the function call I using the ASCII(RIGHT(t.OffensiveColumn,1)) to see what the ASCII character is, then calling the function, and returning the results to a temp table. Then on my temp table, the column that the function was on, I am running the ASCII(RIGHT(r.RemovedASCII) and the values values are the same.
Starting ASCII value 68 - ASCII values after function call is 68.

Without seeing what you're looking at on your screen, this is really hard to interpret. You could make it a lot easier for us by showing the code you are using for this process, in addition to attempting to describe it. Anyway, try this, substituting one of your own actual "dirty" data values for the artificial one used for testing in the script:

DECLARE @MyVar NVARCHAR(100)

SET @MyVar =

'A'+CHAR(1)+

'B'+CHAR(1)+'C'+CHAR(2)+'D'+CHAR(3)+'E'+CHAR(4)+'F'+CHAR(5)+'G'+CHAR(6)+'H'+CHAR(7)+'I'+CHAR(8)+

'J'+CHAR(9)+'K'+CHAR(10)+'L'+CHAR(11)+'M'+CHAR(12)+'N'+CHAR(13)+'O'+CHAR(14)+'P'+CHAR(15)+'Q'+CHAR(16)+

'R'+CHAR(17)+'S'+CHAR(18)+'T'+CHAR(19)+'U'+CHAR(20)+'V'+CHAR(21)+'W'+CHAR(22)+'X'+CHAR(23)+'Y'+CHAR(24)+

'Z'+CHAR(25)+'1'+CHAR(26)+'2'+CHAR(27)+'3'+CHAR(28)+'4'+CHAR(29)+'5'+CHAR(30)+'6'+CHAR(31)+'7'+CHAR(32)

SELECT

OffensiveColumn,

CleanedValue =

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

REPLACE(OffensiveColumn COLLATE LATIN1_GENERAL_BIN,CHAR(0),'')

,CHAR(1),''),CHAR(2),''),CHAR(3),''),CHAR(4),''),CHAR(5),''),CHAR(6),''),CHAR(7),''),CHAR(8),'')

,CHAR(9),''),CHAR(10),''),CHAR(11),''),CHAR(12),''),CHAR(13),''),CHAR(14),''),CHAR(15),''),CHAR(16),'')

,CHAR(17),''),CHAR(18),''),CHAR(19),''),CHAR(20),''),CHAR(21),''),CHAR(22),''),CHAR(23),''),CHAR(24),'')

,CHAR(25),''),CHAR(26),''),CHAR(27),''),CHAR(28),''),CHAR(29),''),CHAR(30),''),CHAR(31),''),CHAR(32),'')

FROM (SELECT OffensiveColumn = @MyVar) d

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

kemboi Newbie Points: 1 More actions · Answer 10

My workaround if you have one space at the end:

update tableName set PayeeName = PayeeName + '_Serengeti' where right(PayeeName,1) = ' '
update tableName set PayeeName = Replace(PayeeName,' _Serengeti','') where PayeeName like '%_Serengeti%'

If you have many spaces, yo may need to execute the statements until the spaces are all removed.