regular expression in sql server select statement

Question

I have a column called external ref in the table transaction

using one row as example

ITS trans code: 1188716 ITS batch: 78606 15/16

1188716 is the trans number
78606 is the batch id
15/16 is the date

I am only interested in the trans and batch id

I originally did

SELECT 
   SUBSTRING(ts.ext_ref, 17, 7) AS transaction_id,
   SUBSTRING(ts.ext_ref, 34, 7) batch_id
   FROM transactions ts

it stopped working because both 1188716 and 78606 are not fixed length I could get transaction id 123 batch id 456 or transaction id 12345566 batch id 45678990

I want to achieve the logic such as paring any continuous number of digits between the 3rd and 4th spaces and the same logic on the 6th and 7th spaces

is it possible to achieve this in SQL server?

Possible duplicate of [A SQL Query to select a string between two known strings](http://stackoverflow.com/questions/18362260/a-sql-query-to-select-a-string-between-two-known-strings) — Tab Alleman, Jun 09 '16 at 15:10
indeed, the use of CHARINDEX() SUBSTRING() REVERSE() LTRIM() REPLACE() functions are the key to solve this problem. — abc the origional, Jun 10 '16 at 10:15

mvisser · Accepted Answer · 2016-06-09T16:12:56.423

1

This is a bit of a hack and this will only work if you receive the data in that format

DECLARE @text VARCHAR(MAX) = 'ITS trans code: 1188716 ITS batch: 78606 15/16'

SELECT LEFT(LTRIM(RTRIM(REPLACE(@text, LEFT(@Text, CHARINDEX(':', @text)),   ''))), CHARINDEX(' ',LTRIM(RTRIM(REPLACE(@text, LEFT(@text, CHARINDEX(':',  @text)), ''))))) As TransNumber, 
REVERSE(LEFT(REPLACE(REVERSE(@text),  LEFT(REVERSE(@text), CHARINDEX(' ',REVERSE(@text))), ''),CHARINDEX(' ',REPLACE(REVERSE(@text), LEFT(REVERSE(@text), CHARINDEX(' ', REVERSE(@text))), '')))) AS BatchID

edited Jun 09 '16 at 16:12

answered Jun 09 '16 at 15:19

mvisser

652
5
11

sorry. What you do is reverse it back. I did make an update. – mvisser Jun 09 '16 at 16:12

score 1 · Answer 2 · answered Jun 09 '16 at 15:46

We faced the same problem and fixed it using Master Data Services Functions. We really needed RegExp on SqlServer and finally got a function like this one:

CREATE FUNCTION [RegexExtract](@input [nvarchar](4000), @pattern [nvarchar](4000), @group [nvarchar](4000), @mask [tinyint])
RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER, RETURNS NULL ON NULL INPUT
AS 
EXTERNAL NAME [Microsoft.MasterDataServices.DataQuality].[Microsoft.MasterDataServices.DataQuality.SqlClr].[RegexExtract]
GO

Follow this link to get more info: https://dyball.wordpress.com/2011/11/01/sql-2008-r2-regular-expressions/

i would upvote if I can.. this is one of the good solution. but in my case it's a one off query. mvisser's answer suits me better — abc the origional, Jun 10 '16 at 08:47

regular expression in sql server select statement

2 Answers2