Regular expressions in SQL

Question

Im curious if and how you can use regular expressions to find white space in SQL statments.

I have a string that can have an unlimited amount of white space after the actual string.

For example:

"STRING  "
"STRING "

would match, but

 
"STRING A"
"STRINGB"

would not.

Right now I have:

like 'STRING%'

which doesnt quite return the results I would like.

I am using Sql Server 2008.

Answer 1

BOL provides workarounds for LEN() with trailing spaces : http://msdn.microsoft.com/en-us/library/ms190329.aspx

LEN(Column + '_') - 1

or using DATALENGTH

Answer 2

A simple like can find any string with spaces at the end:

where col1 like '% '

To also allow tabs, carriage returns or line feeds:

where col1 like '%[ ' + char(9) + char(10) + char(13) + ']'

Per your comment, to find "string" followed by any number of whitespace:

where rtrim(col1) = 'string'

Answer 3

where len(col1 + 'x') <> len(rtrim(col1)) + 1

Answer 4

Andomar's answer will find the strings for you, but my spidey sense tells me maybe the scope of the problem is bigger than simply finding the whitespace.

If, as I suspect, you are finding the whitespace so that you can then clean it up, a simple

UPDATE Table1
SET col1 = RTRIM(col1)

will remove any trailing whitespace from the column.

Or RTRIM(LTRIM(col1)) to remove both leading and trailing whitespace.

Or REPLACE(col1,' '.'') to remove all whitespace including spaces within the string.

Note that RTRIM and LTRIM only work on spaces, so to remove tabs/CRs/LFs you would have to use REPLACE. To remove those only from the leading/trailing portion of the string is feasible but not entirely simple. Bug your database vendor to implement the ANSI SQL 99 standard TRIM function that would make this much easier.

Answer 5

You could try

where len(col1) <> len(rtrim(col1))