Does rtrim remove whitespace other than spaces?

This select yields the results below.  rtrim doesn't seem to trim the last character.  I'm wondering if rtrim removes spaces but not tabs or something.

select len(loginwindows) as loginlen, len(rtrim(loginwindows)) as loginlenrtrim, left(loginwindows, 8) as login8, loginwindows from general.employees where lastname = 'langley'

loginlen      loginlentrim    login8           loginwindows
9                9                      LL004838      LL004838 
Barry SweezeySoftware EngineerAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
Re-post of query:

Try this query to identify the trailing char(s) :

SELECT
    DISTINCT
    ASCII(RIGHT(LoginWindows, 1))
FROM general.employees
WHERE
    ASCII(RIGHT(LoginWindows, 1)) < 32 OR
    ASCII(RIGHT(LoginWindows, 1)) > 127
0
 
Scott PletcherSenior DBACommented:
RTRIM/LTRIM remove only spaces.
0
 
Barry SweezeySoftware EngineerAuthor Commented:
How do I remove all whitespace?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Scott PletcherSenior DBACommented:
Easiest way to remove others is to use REPLACE ... but if you only want to replace trailing values it's quite a bit trickier.

To remove *all* tabs, carriage returns and line feeds, you can do this:

UPDATE
SET column = REPLACE(REPLACE(REPLACE(column, CHAR(9), ''), CHAR(13), ''), CHAR(10), '')
WHERE
    column LIKE '%' + CHAR(9) + '%' OR
    column LIKE '%' + CHAR(13) + '%' OR
    column LIKE '%' + CHAR(10) + '%'
0
 
Scott PletcherSenior DBACommented:
If you want to be really thorough, you could also check for CHAR(11) and CHAR(15), although the ones above are the most common in Windows.
0
 
Barry SweezeySoftware EngineerAuthor Commented:
Thanks for the quick response.

I ran the query below and received the message (0 row(s) affected).  The original query yields the sames results.  How can I identify the last character in the string?


UPDATE general.employees
SET LoginWindows = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LoginWindows, CHAR(9), ''), CHAR(10), ''), CHAR(11), ''), CHAR(13), ''), CHAR(15), '')
WHERE
    LoginWindows LIKE '%' + char(9) + '%' OR
    LoginWindows LIKE '%' + char(10) + '%' OR
    LoginWindows LIKE '%' + char(10) + '%' OR
    LoginWindows LIKE '%' + char(13) + '%' OR
    LoginWindows LIKE '%' + char(15) + '%'
0
 
Scott PletcherSenior DBACommented:
SELECT
    DISTINCT
    ASCII(RIGHT(LoginWindows, 1))
FROM general.employees
WHERE
    ASCII(RIGHT(LoginWindows, 1)) < 32 OR
    ASCII(RIGHT(LoginWindows, 1)) > 127
0
 
Barry SweezeySoftware EngineerAuthor Commented:
I corrected a line; no difference.

    LoginWindows LIKE '%' + char(10) + '%' OR
    LoginWindows LIKE '%' + char(11) + '%' OR
0
 
Barry SweezeySoftware EngineerAuthor Commented:
Results:

(No column name)
160

So it's a Non-breaking space.

I updated the update query (below) and it worked.

UPDATE general.employees
SET LoginWindows = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LoginWindows, CHAR(9), ''), CHAR(10), ''), CHAR(11), ''), CHAR(13), ''), CHAR(15), ''), CHAR(160), '')
WHERE
    LoginWindows LIKE '%' + char(9) + '%' OR
    LoginWindows LIKE '%' + char(10) + '%' OR
    LoginWindows LIKE '%' + char(11) + '%' OR
    LoginWindows LIKE '%' + char(13) + '%' OR
    LoginWindows LIKE '%' + char(15) + '%' OR
    LoginWindows LIKE '%' + char(160) + '%'
0
 
Barry SweezeySoftware EngineerAuthor Commented:
I appreciate ScottPletcher's hanging with me until the problem was solved.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.