Solved

Does rtrim remove whitespace other than spaces?

Posted on 2012-03-29
10
354 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:ScottPletcher
ScottPletcher 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:ScottPletcher
ScottPletcher 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
 
LVL 69

Expert Comment

by:ScottPletcher
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher 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:
ScottPletcher 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now