locked
RTRIM Not working RRS feed

  • Question

  • Hi guys,

    I have a strange issue when trying to trim some trailing spaces off a string.

    When I do

    select

     

    RTRIM([ErrorDescription]) from [employer].[T_BulkUpLoadNewStarters_Errors]

    I get (note the two extra spaces).

    'The data value cannot be converted for reasons other than sign mismatch or data overflow.  '

    Why is it not stripping off the last two extra spaces? It's causing my join to not return any results when I know there is a match there.

    Thanks for your help.

    Monday, March 14, 2011 11:59 AM

Answers

  • The last two characters are a Carriage Return and a Line Feed respectively.

    One way of removing them is with two REPLACE function calls:

    REPLACE(REPLACE(  my_value, CHAR(13),''), CHAR(10),'')

     


    Gert-Jan
    • Marked as answer by wattyjnr Thursday, March 17, 2011 5:42 PM
    Monday, March 14, 2011 9:35 PM

All replies

  • Are you sure the last 2 characters are really spaces and not some other invisible characters? Try checking

     

    ASCII(right(ErrorDescription),1)
    
    and 
    
    ASCII(substring(ErrorDescription, len(ErrorDescription)-2,1))
    

     


    For every expert, there is an equal and opposite expert. - Becker's Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, March 14, 2011 12:02 PM
  • Are you sure those are Spaces, whitespace i.e. char(32). May be other than white space, check the ASCII code for those spaces.

    select ASCII(RIGHT([ErrorDescription],1)) from [employer].[T_BulkUpLoadNewStarters_Errors]
    


    Why not clean up and remove the spaces from your tables itself.


    ~Manu
    http://sqlwithmanoj.wordpress.com
    MCCA 2011
    Monday, March 14, 2011 12:08 PM
  • Perhaps that the whitespace characters are not spaces but some other whitespace.  Run the query below and examine the last few characters to verify the value is 0x2020:

    SELECT TOP (1) CAST(ErrorDescription AS varbinary(MAX))
    FROM [employer].[T_BulkUpLoadNewStarters_Errors]
    WHERE ErrorDescription LIKE 'The data value cannot be converted for reasons other than sign mismatch or data overflow%';
    
    
    

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Monday, March 14, 2011 12:15 PM
  • If you are 100% sure you dont need last TWO chars, you can always remove them regardless.

    LEFT(LEN(ErrorDescription)-2)


    If you think my suggestion is useful, please rate it as helpful.
    If it has helped you to resolve the problem, please Mark it as Answer.

    Sevengiants.com
    Monday, March 14, 2011 12:46 PM
  • I copied the characters from your results and pasted them into SSMS with the following check...

    Select ASCII(' ') for the first character, which returned 32 (a space) and for the second character, which was not a space and would not paste into SSMS.  It's that last character in the message that is your problem and it's also the reason why the second to last character (the space) is not being trimmed from the string.  You need to remove that last character. 

    Try to identify that last character and replace any occurrences of those characters with strings, then trim your data in the database.

    In my browser, I selected the last character and then right clicked and selected copy.  In SSMS, I right clicked and selected paste, and it wouldn't paste anything.  It's an unusual non-space character.

     

    -Eric Isaacs

     

     

    Monday, March 14, 2011 4:19 PM
  • Hi all, sorry for late response, got caught up with other stuff. I get the 32 when I run the below query

    select

     

    ascii(right('The data value cannot be converted for reasons other than sign mismatch or data overflow.  ',1)).

    Is that correct?

    Monday, March 14, 2011 6:04 PM
  • It complains about invalid parameter when i run that query?
    Monday, March 14, 2011 6:04 PM
  • I get the below value when I run this query;

    0x540068006500200064006100740061002000760061006C00750065002000630061006E006E006F007400200062006500200063006F006E00760065007200740065006400200066006F007200200072006500610073006F006E00730020006F00740068006500720020007400680061006E0020007300690067006E0020006D00690073006D00610074006300680020006F0072002000640061007400610020006F0076006500720066006C006F0077002E000D000A00

    Monday, March 14, 2011 6:05 PM
  • Ok thanks Eric. Can I simply just trim the last character off the end of the string?
    Monday, March 14, 2011 6:07 PM
  • Can you run the queries we suggested, e.g. the query against your table's ErrorDescription field instead? Also, what is the type of this field?

     


    For every expert, there is an equal and opposite expert. - Becker's Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, March 14, 2011 6:20 PM
  • The last two characters are a Carriage Return and a Line Feed respectively.

    One way of removing them is with two REPLACE function calls:

    REPLACE(REPLACE(  my_value, CHAR(13),''), CHAR(10),'')

     


    Gert-Jan
    • Marked as answer by wattyjnr Thursday, March 17, 2011 5:42 PM
    Monday, March 14, 2011 9:35 PM
  • Gert, that worked perfectly!! Thank you very much.
    Thursday, March 17, 2011 5:41 PM
  • but it is working on local DB but not working on Production DB...?
    Thursday, August 20, 2020 9:48 AM
  • Rather than add on to an old answered thread, it's best to ask a new question for your specific problem if the answer here didn't work. That will increase visibility and likelihood of an answer for your situation. Consider posting questions on Microsoft Q&A going forward with the "sql-server-transact-sql" tag since this forum is being migrated and will soon become read-only.

    It seems the actual value in production is different than your localDB if the same T-SQL code yields different results with TRIM or REPLACE. The query below will show the binary value of the last 2 characters. Specify the decimal value of the non-space whitespace characters (i.e. tab, carriage return, line feed, etc) in the CHAR function(s).

    SELECT CAST(RIGHT(YourColumn, 2) AS varbinary(4)) AS BinaryValueOfLast2Characters
    FROM dbo.YourTable;


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com


    • Edited by Dan GuzmanMVP Thursday, August 20, 2020 11:14 AM
    • Proposed as answer by Naomi N Thursday, August 20, 2020 2:29 PM
    Thursday, August 20, 2020 11:13 AM