Using a RegEx in a SQL Query

Question

Hey Everyone,

Here's the situation I'm in: We have a field in our database that contains a 3 digit number, surrounded by some text. This number is actually a PK in another table, and I need to extract this out so I can implement a proper FK relationship. Here's an example of what would currently reside in the column:

Some Text Goes Here - (305) Followed By Some More Text

So, what I'm looking to do is extract the '305' from the column, and hopefully end up with a result that looks something like this (pseudo code)

SELECT
   <My Extracted Value>,
   Original Column Text,
   Id
FROM dbo.MyTable

It seems to me that using a Regex match in my query is the most effective way to do this. Can anybody point me in the right direction?

EDIT: We're using SQL Server 2005

Answer 1

RegExp in SQL is defined by a SQL-Standard but most databases implemented their own syntax, you should tell us the product name of your RDBMS ;)

Answer 2

Try this :Extract only numbers from a String

DECLARE @NumStr varchar(1000) 
SET @NumStr = 'get 9 and 3 from this'; 
SELECT SUBSTRING(@NumStr,PATINDEX('%[0-9]%',@NumStr),1)

Answer 3

Microsoft seems to suggest using a CLR assembly to do Regex pattern matching in SQL Server 2005.

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

Apart from LIKE (which is not going to solve your problem) I don't know of a built-in pattern matching functionality in SQL Server 2005 (that is, more advanced than simple string searches).

Answer 4

Just after I implemented a solution in Postgres, I see you are using SqlServer... Just for the records, then, with a regex that extracts data in parenthesis.

Postgresql solution:

create table main(id text not null)
insert into main values('some text (44) other text');
insert into main values('and more text (78) and even more');

select substring(id from '\\(([^\\(]+)\\)') from main

Answer 5

This is based on Pranay's first answer that has since been changed.

DECLARE @NumStr varchar(1000)
SET @NumStr = 'Some Text Goes Here - (305) Followed By Some More Text';
SELECT SUBSTRING(@NumStr,PATINDEX('%[0-9][0-9][0-9]%',@NumStr),3)

Returns 305

Answer 6

The only way to access RegEx-type functions in SQL 2005 (and probably 2008) is by writing (or downloading) and using CLR functions.

If all the strings are always formatted in such a way as you can identify the specific numbers you want, you can do something like the following. This is based on the (big) assumption that the first set of parenthesis found in the string contains the number that you want.

/*

CREATE TABLE MyTable
 (
   MyText  varchar(500)  not null
 )

INSERT MyTable values ('Some Text Goes Here - (305) Followed By Some More Text')

*/

SELECT
   MyText  --  String
  ,charindex('(', MyText)  --  Where's the open parenthesis
  ,charindex(')', MyText)  --  Where's the closed parenthesis
  ,substring(MyText
             ,charindex('(', MyText) + 1, charindex(')'
             ,MyText) - charindex('(', MyText) - 1)  --  Glom it all together
 from MyTable

Awkward as heck (because SQL has a pathetically limited set of string manipulation functions), but it works.