How to match trailing whitespace
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
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
ASKER
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?
Would you mind giving me an example of how to use this in a query using LIKE to find the correct records?
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
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
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
http://msdn.microsoft.com/en-us/library/aa933232%28v=sql.80%29.aspx
HTH,
Philippe
ASKER
Pefect thank you!
You can user LTRIM and RTRIM to remove trailing and leading spaces respectively.
HTH,
Philippe