CODEWITHSUNDEEP
regexsql-server-2005
Jim B
Jim B

Reputation: 8574

Using a RegEx in a SQL Query

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

Upvotes: 0

Views: 2070

Answers (5)

Philip Kelley
Philip Kelley

Reputation: 40289

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.

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 452977

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

Upvotes: 0

small_duck
small_duck

Reputation: 3096

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

Upvotes: 0

Lucero
Lucero

Reputation: 60190

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).

Upvotes: 0

Tobias P.
Tobias P.

Reputation: 4665

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 ;)

Upvotes: 1

Related Questions