CODEWITHSUNDEEP
sql-serverpatindex
vvanasperen
vvanasperen

Reputation: 53

Is there a simplification of this patindex code possible?

I've been looking for a way to simplify the patindex code below, but haven't succeeded yet. Is there anyone who can tell me if this is possible witin SQL Server.

patindex('%'+'('+'[0-9][0-9]'+'/'+'[0-9][0-9]'+'/'+'[0-9][0-9][0-9][0-9]'+' - '+'[0-9][0-9]'+'/'+'[0-9][0-9]'+'/'+'[0-9][0-9][0-9][0-9]'+')'+'%', ii.description)

The string always contains the date part like (17/09/2015 - 16/09/2016) and I want to get the two dates from that string. The idea is that I look for the starting point of this text block with the two dates and then calculate with a substring function (and some math) where and what the start- and enddate are.

If there is a simpler or better way to do this, I'm open for suggestions. Thanks a lot.

Upvotes: 0

Views: 45

Answers (1)

Alan Burstein
Alan Burstein

Reputation: 7918

There's a few ways to clean up what you have and many ways to solve this.

DECLARE @text VARCHAR(100) = '(17/09/2015 - 16/09/2016)';

--==== Solution #1: Basic position matching
SELECT 
  Date1 = SUBSTRING(@text,2,10),
  Date2 = SUBSTRING(@text,15,10);

--==== Solution #2: Cleanup using APPLY
SELECT TheDate = PATINDEX('%('+fmt.Phone+' - '+fmt.Phone+')%', @text)
FROM   (VALUES('[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]')) AS fmt(Phone);

--==== Solution #3: Use 2nd APPLY call for the Second Date; MUCH CLEANER!
SELECT
  Date1 = SUBSTRING(@text,p1.Pos,10), 
  Date2 = SUBSTRING(@text,p1.Pos+12,10)
FROM        (VALUES('%[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]%')) AS fmt(Phone)
CROSS APPLY (VALUES(PATINDEX(fmt.Phone,@text)))                      AS p1(Pos)
CROSS APPLY (VALUES(SUBSTRING(@text,p1.Pos+12,10)))                  AS st2(Txt)

These three queries return:

Date1      Date2
---------- ----------
17/09/2015 16/09/2016

TheDate (location of first date)
-----------
1

Date1      Date2
---------- ----------
17/09/2015  16/09/201

Stuff like this is very simple and fast when using NGrams8k.

--==== Solution #3 Ngrams8k
SELECT      TheDate = f.Dt
FROM        dbo.ngrams8k(@text,10)                   AS ng
CROSS APPLY (VALUES(TRY_CONVERT(DATE,ng.token,103))) AS f(Dt)
WHERE       CHARINDEX('/',ng.token) = 3
  AND       f.Dt IS NOT NULL;

--==== Solution #4 Ngrams8k with Pivot
SELECT
  Date1 = MAX(CASE f.RN WHEN 1 THEN f.DT END),
  Date2 = MAX(CASE f.RN WHEN 2 THEN f.DT END)
FROM
(
  SELECT      ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), f.Dt
  FROM        dbo.ngrams8k(@text,10)                   AS ng
  CROSS APPLY (VALUES(TRY_CONVERT(DATE,ng.token,103))) AS f(Dt)
  WHERE       CHARINDEX('/',ng.token) = 3
    AND       f.Dt IS NOT NULL
)  AS f(RN,DT);

These return:

TheDate
----------
2015-09-17
2016-09-16

Date1      Date2
---------- ----------
2015-09-17 2016-09-16

The first solution returns the dates as rows, the second is pivoted. Whichever you need.

Upvotes: 1

Related Questions