Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

LTRIM and RTRIM not working Expand / Collapse
Author
Message
Posted Wednesday, May 28, 2014 8:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 11:29 AM
Points: 128, Visits: 549
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.
Post #1575209
Posted Wednesday, May 28, 2014 8:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 12,995, Visits: 12,414
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 Moden's 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)
Post #1575218
Posted Wednesday, May 28, 2014 8:39 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 22, 2014 2:44 PM
Points: 1,369, Visits: 767
Try this to determine what the end character is.

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

Post #1575224
Posted Wednesday, May 28, 2014 8:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 11:29 AM
Points: 128, Visits: 549
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.
Post #1575231
Posted Wednesday, May 28, 2014 9:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 12,995, Visits: 12,414
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 Moden's 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)
Post #1575237
Posted Wednesday, May 28, 2014 9:58 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:03 AM
Points: 20,703, Visits: 32,344
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.



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)
Post #1575259
Posted Thursday, May 29, 2014 8:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 11:29 AM
Points: 128, Visits: 549
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...
Post #1575610
Posted Thursday, May 29, 2014 8:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:37 AM
Points: 6,748, Visits: 13,890
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
Post #1575620
Posted Thursday, May 29, 2014 8:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 11:29 AM
Points: 128, Visits: 549
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.
Post #1575621
Posted Thursday, May 29, 2014 9:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:37 AM
Points: 6,748, Visits: 13,890
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
Post #1575654
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse