Monthly Archives: July 2010

Using PATINDEX to scrub data…

I was reading the local NTSSUG (North Texas Sql Server Users Group) and found a query that was a prime candidate for a regular expression, but another user suggested the use of PATINDEX.

The problem was embedded data that had a specific format: two alpha chars followed by 4 or 5 digits.

I tried a little test using PATINDEX and it worked very well:


DECLARE @zot6 varchar(7),

@zot7 varchar(7),

@test1 varchar(20),

@test2 varchar(20);

set @test1 = 'Ref PO:NY1234';

set @test2 = 'PO#MN12345XYZ';

SELECT @zot6 = SUBSTRING(@test1,PATINDEX('%[A-Z][A-Z][0-9][0-9][0-9][0-9]%',@test1),6);

SELECT @zot7 = SUBSTRING(@test1,PATINDEX('%[A-Z][A-Z][0-9][0-9][0-9][0-9][0-9]%',@test1),7);

IF (DATALENGTH(@zot7) = 7)

SELECT @zot7

ELSE

SELECT @zot6

SELECT @zot6 = SUBSTRING(@test2,PATINDEX('%[A-Z][A-Z][0-9][0-9][0-9][0-9]%',@test2),6);

SELECT @zot7 = SUBSTRING(@test2,PATINDEX('%[A-Z][A-Z][0-9][0-9][0-9][0-9][0-9]%',@test2),7);

IF (DATALENGTH(@zot7) = 7)

SELECT @zot7

ELSE

SELECT @zot6

Advertisements