Solved

Does rtrim remove whitespace other than spaces?

Posted on 2012-03-29
10
358 Views
Last Modified: 2012-03-29
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 
0
Comment
Question by:Barry Sweezey
  • 5
  • 5
10 Comments
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 500 total points
ID: 37784219
RTRIM/LTRIM remove only spaces.
0
 

Author Comment

by:Barry Sweezey
ID: 37784222
How do I remove all whitespace?
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 500 total points
ID: 37784233
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37784251
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
 

Author Comment

by:Barry Sweezey
ID: 37784392
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
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 500 total points
ID: 37784409
SELECT
    DISTINCT
    ASCII(RIGHT(LoginWindows, 1))
FROM general.employees
WHERE
    ASCII(RIGHT(LoginWindows, 1)) < 32 OR
    ASCII(RIGHT(LoginWindows, 1)) > 127
0
 

Author Comment

by:Barry Sweezey
ID: 37784418
I corrected a line; no difference.

    LoginWindows LIKE '%' + char(10) + '%' OR
    LoginWindows LIKE '%' + char(11) + '%' OR
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 37784581
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
 

Author Comment

by:Barry Sweezey
ID: 37784979
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
 

Author Closing Comment

by:Barry Sweezey
ID: 37784994
I appreciate ScottPletcher's hanging with me until the problem was solved.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question