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 — Tab Alleman
– Tab Alleman, Commented Jun 9, 2016 at 15:10
indeed, the use of CHARINDEX() SUBSTRING() REVERSE() LTRIM() REPLACE() functions are the key to solve this problem. — abc the origional
– abc the origional, Commented Jun 10, 2016 at 10:15

mvisser · Accepted Answer · 2016-06-09 16:12:56Z

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 9, 2016 at 16:12

answered Jun 9, 2016 at 15:19

mvisser

6705 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mvisser Over a year ago

sorry. What you do is reverse it back. I did make an update.

vercelli · Accepted Answer · 2016-06-09 15:46:58Z

1

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/

answered Jun 9, 2016 at 15:46

vercelli

4,7772 gold badges17 silver badges15 bronze badges

1 Comment

abc the origional Over a year ago

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

Collectives™ on Stack Overflow

regular expression in sql server select statement

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related