Can the Select list in a SQL Statement use Regular Expressions

Question

I have a SQL statement,

select ColumnName from Table

And I get this result,

Error 192.168.1.67 UserName 0bce6c62-1efb-416d-bce5-71c3c8247b75 An existing ....

So anyway the field has a lot of stuff in it, I just want to get out the 'UserName'.

Can I use a regex for that?

I mean it would be kind of like this,

select SUBSTRING(ColumnName, 0, 5) from Table

Except the SUBSTRING would be replaced with a regex of some kind. I am comfortable with regex, but I am not sure how to apply it in this case, or even if you can.

If I could get this working it would be great because I plan to pull the data into a temporary table, and do some quite complicated things matching it with other tables etc. If I can get this all working it would save me writing a C# app to do it with.

Thanks.

possible duplicate of [regular expression inside SQL Server](http://stackoverflow.com/questions/1964124/regular-expression-inside-sql-server) — Michael Mrozek, Jun 25 '10 at 05:03
useful read: [tsql regular expression workbench](http://www.simple-talk.com/sql/t-sql-programming/tsql-regular-expression-workbench/) — Christopher Klein, Jun 29 '10 at 12:51

score 1 · Accepted Answer · answered Jun 25 '10 at 05:02

1

No, out of the box, SQL Server doesn't support regexs.

You could retrofit those by means of a SQL-CLR assembly that you deploy into SQL Server.

answered Jun 25 '10 at 05:02

marc_s

732,580
175
1,330
1,459

d_schnell · Answer 2 · 2010-06-28T16:06:00.677

I think going you should use SUBSTRING anyway. Using regular expression is more flexible but also lead to a large processing overhead. This becomes even worse if your have to process a large recordsets.

You have to justify if there's the need for flexibility in first place.

If so you should read about it here:

http://msdn.microsoft.com/en-us/magazine/cc163473.aspx

Using T-SQL only can look like that:

SELECT 'Error 192.168.1.67 XUserNameX 0bce6c62-1efb-416d-bce5-71c3c8247b75 An existing' expr
    INTO log_table
GO
WITH
    split1 (expr, cstart, cend) 
AS (
        SELECT
            expr, 1, 0
        FROM
            log_table a
), split2 (expr, cstart, cend, div)
AS (
    SELECT
        a.expr, a.cend + 1, CHARINDEX(' ', a.expr, a.cend + 1), 1 
    FROM
        split1 a
    UNION ALL
    SELECT
        a.expr, a.cend + 1, CHARINDEX(' ', a.expr, a.cend + 1), div+1
    FROM
        split2 a
    WHERE
        a.cend > 1
), substrings(expr, div)
AS (
    SELECT 
        SUBSTRING(expr, cstart, cend - cstart), div
    FROM
        split2
)
SELECT expr from
    substrings a
where
    a.div = 3

I once would have answered much the same. However, now, I can say with a great deal of certainty - regex it. I won't try to justify myself too much. I'll just say that the string manipulation tools in T-SQL leave much to be desired, C# and its implementation of regex are wonderful, and with T-SQL, using substring / charindex etc, requires one to write a separate extraction for each piece of data you want from your string. With Regex, one pattern and you're done. Much simpler to maintain. Right, maintainable & "slow" beats Wrong, confusing & "fast" any day. — Jeff Maass, Jun 26 '10 at 04:16

onedaywhen · Answer 3 · 2010-06-29T12:41:57.650

UPDATE

we cannot tell where the start of the username is. Unless we can say 'find me the start character after the second space'

That is fairly straightforward:

Filter out strings that have fewer than two spaces (alternatively, have three or more words);
Find the position after the first space (alternatively, the beginning of the second word);
Find the position after the the first space after the first space (alternatively, the beginning of the third word);
Determine the length of the third word using the position of the next space (or the end of the string is there are only three words);
Use the above values with the SUBSTRING() function to return the third word.

Example:

WITH MyTable (ColumnName)
AS
(
 SELECT NULL
 UNION ALL 
 SELECT ''
 UNION ALL 
 SELECT 'One.'
 UNION ALL 
 SELECT 'Two words.'
 UNION ALL 
 SELECT 'Three word sentence.'
 UNION ALL 
 SELECT 'Sentence containing four words.'
 UNION ALL 
 SELECT 'Five words in this sentence.'
 UNION ALL 
 SELECT 'Sentence containing more than five words.'
), 
AtLeastThreeWords (ColumnName, pos_word_2_start)
AS
(
 SELECT M1.ColumnName, CHARINDEX(' ', M1.ColumnName) + LEN(' ') + 1
   FROM MyTable AS M1
  WHERE LEN(M1.ColumnName) - LEN(REPLACE(M1.ColumnName, ' ', '')) >= 2
), 
MyTable2 (ColumnName, pos_word_3_start)
AS
(
 SELECT M1.ColumnName, 
        CHARINDEX(' ', M1.ColumnName, pos_word_2_start) + LEN(' ') + 1
   FROM AtLeastThreeWords AS M1
), 
MyTable3 (ColumnName, pos_word_3_start, pos_word_3_end)
AS
(
 SELECT M1.ColumnName, M1.pos_word_3_start, 
        CHARINDEX(' ', M1.ColumnName, pos_word_3_start) + LEN(' ')
   FROM MyTable2 AS M1        
), 
MyTable4 (ColumnName, pos_word_3_start, word_3_length)
AS
(
 SELECT M1.ColumnName, M1.pos_word_3_start, 
        CASE 
           WHEN pos_word_3_start < pos_word_3_end 
              THEN pos_word_3_end - pos_word_3_start
           ELSE LEN(M1.ColumnName) - pos_word_3_start + 1
        END         
   FROM MyTable3 AS M1        
)
SELECT M1.ColumnName, 
       SUBSTRING(M1.ColumnName, pos_word_3_start, word_3_length) 
          AS word_3
  FROM MyTable4 AS M1;

ORIGINAL ANSWER:

Is the problem that the position and/or length of the username value may not be constant in the data but always follows the string 'username '? If so, you can use CHARINDEX with SUBSTRING e.g.

WITH MyTable (ColumnName)
AS
(
 SELECT 'Error 192.168.1.67 UserName 0bce6c62-1efb-416d-bce5-71c3c8247b75 An existing ....'
 UNION ALL 
 SELECT 'Username onedaywhen is invalid'

), 
MyTable1 (ColumnName, pos1)
AS
(
 SELECT M1.ColumnName, CHARINDEX('UserName ', M1.ColumnName) + LEN('UserName ') + 1
   FROM MyTable AS M1
), 
MyTable2 (ColumnName, pos1, pos2)
AS
(
 SELECT M1.ColumnName, M1.pos1, 
        CHARINDEX(' ', M1.ColumnName, pos1) - M1.pos1
   FROM MyTable1 AS M1
)
SELECT SUBSTRING(M1.ColumnName, M1.pos1, M1.pos2)
  FROM MyTable2 AS M1;

...though you'd need to make it more robust e.g. when there is no trailing space after the username value etc.

The IP address could be any value. I.e. the above IP address 192.168.1.89 is 12 characters long, and 10.5.4.3 is only 8 characters long. Also the string 'Username' will not be in the field. It will just be an actual username 'jerryb' or 'georget' etc. What I am getting at is that we cannot tell where the start of the username is. Unless we can say 'find me the start character after the second space'. Does that make sense? — peter, Jun 27 '10 at 21:15

Can the Select list in a SQL Statement use Regular Expressions

3 Answers3