Solved

How to match trailing whitespace

Posted on 2012-04-02
7
346 Views
Last Modified: 2012-06-27
I have a field that I need to match the string however there is trailing white space on the end.

How do I grab everything that matches the expression including any trailing whitespace?

WHERE field = 'S-34.5X3.5M' should return the same result as WHERE filed LIKE 'S-34.5X3.5M' however due to the trailing white space the first returns a result while the second does not.

I do not want things like 'S-34.5X3.5MH' and 'S-34.5X3.5M R S  RM' returned
0
Comment
Question by:turn123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 9

Expert Comment

by:Philippe Damerval
ID: 37798022
Hi,
You can user LTRIM and RTRIM to remove trailing and leading spaces respectively.

HTH,

Philippe
0
 
LVL 11

Author Comment

by:turn123
ID: 37798033
Thank you Philippe.

Would you mind giving me an example of how to use this in a query using LIKE to find the correct records?
0
 
LVL 9

Expert Comment

by:Philippe Damerval
ID: 37798333
Sure, although the idea here is specifically not to use LIKE (in order to avoid returning the values you don't want).

SELECT field1, field2 from MYTABLE
WHERE RTRIM(field) = 'S-34.5X3.5M'

If your database is case sensitive (off by default) then you may want to use UPPER to convert all letters to uppercase first, to ensure matches for all cases:

WHERE UPPER(RTRIM(field)) = 'S-34.5X3.5M'

HTH,

Philippe
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 11

Author Comment

by:turn123
ID: 37798513
Hi Philippe,

Specifically my problem is that I need to use LIKE. The example here had the wild card replaced to show the problem (the record exists but LIKE won't pick it up even with the EXACT SAME value as the = query as due to the trailing white space).  I apologize if I was unclear.

The actual LIKE ''%-34.5X3.5M'' where % could be quite a few different values and more values could be added in the future.

Your thoughts?
0
 
LVL 9

Accepted Solution

by:
Philippe Damerval earned 250 total points
ID: 37798595
Have you tried

WHERE UPPER(RTRIM(field)) LIKE '%-34.5X3.5M' ?
0
 
LVL 9

Expert Comment

by:Philippe Damerval
ID: 37798604
P.S. You can use the underscore (_) character to match a single character, as well as braces ([]) to match any character within a range. More detail, and some great examples at
http://msdn.microsoft.com/en-us/library/aa933232%28v=sql.80%29.aspx

HTH,

Philippe
0
 
LVL 11

Author Closing Comment

by:turn123
ID: 37798901
Pefect thank you!
0

Featured Post

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.

Question has a verified solution.

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

I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

738 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