LTRIM and RTRIM not working

  • GBeezy

    SSCarpal Tunnel

    Points: 4468

    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

    SSC Guru

    Points: 286324

    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

    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: 4468

    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: 286324

    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: 442073

    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: 4468

    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: 186019

    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?

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


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

  • GBeezy

    SSCarpal Tunnel

    Points: 4468

    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: 186019

    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

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


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

  • kemboi

    Newbie

    Points: 1

    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.

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

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