SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


LTRIM and RTRIM not working


LTRIM and RTRIM not working

Author
Message
GBeezy
GBeezy
SSC Eights!
SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)

Group: General Forum Members
Points: 845 Visits: 711
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.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64676 Visits: 17979
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
eccentricDBA
eccentricDBA
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2239 Visits: 1354
Try this to determine what the end character is.

SELECT ASCII(RIGHT(<your column>,1)) FROM <your table> WHERE <some selection criteria>
GBeezy
GBeezy
SSC Eights!
SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)

Group: General Forum Members
Points: 845 Visits: 711
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
Sean Lange
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64676 Visits: 17979
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97927 Visits: 38993
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.


Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
GBeezy
GBeezy
SSC Eights!
SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)

Group: General Forum Members
Points: 845 Visits: 711
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
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42787 Visits: 20015
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
Exploring Recursive CTEs by Example Dwain Camps
GBeezy
GBeezy
SSC Eights!
SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)SSC Eights! (845 reputation)

Group: General Forum Members
Points: 845 Visits: 711
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
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42787 Visits: 20015
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(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
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
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search