CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012sql-like
Petrik
Petrik

Reputation: 827

LIKE pattern T-SQL

I have wrote a phraser which is phrasing a quite long string into small pieces, after the phrasing of one item is completed, it removes him from @input and continue, until it wont be able to find any items to phrase. I am selecting items based on LIKE pattern.

In some cases, there are however it is selecting some other parts of the message, and then it's end in infinitive loop.

The pattern I am looking for to be selected using LIKE clause is in format of :

(Any number from 1 to 9) + (variable length A-Z only) + '/' + (variable length A-Z only)+space of Cr or Lf or CrLf.

--This is what I do have: 
DECLARE @match NVarChar(100)
    SET @match  =  '%[1-9][a-z]%'

DECLARE @input1 varchar(max),@input2 varchar(max)  
    SET @input1 ='1ABCD/EFGH *W/17001588 *RHELLO SMVML1C'

DECLARE @position Int 
    SET @position = PATINDEX(@match, @input1);

SELECT @position;

--after the loop- it is also 'catching' the 1C at the end of the string: 

SET @input2     = '*W/17001588 *RHELLO SMVML1C'
SET @position   = PATINDEX(@match, @input2);

SELECT @position

---In order to eliminate this, I have tried to change @match:

SET @match  =  '%[1-9][a-z][/][a-z]%'

SET @position = PATINDEX(@match, @input1);
SELECT @position  --postion is  0, so the first item, that should have been selected, wasn't selected
SET @position   = PATINDEX(@match, @input2);
SELECT @position --postion is  0

Many thanks for help!

Upvotes: 7

Views: 605

Answers (3)

TPrince
TPrince

Reputation: 1

I don't know if this solves your entire problem , but if you can prefix your input with a space, you can modify the pattern to avoid matching a number without a preceding space.

set @input = ' '+@input;
set @match = '% [1-9][a-z]%';

If you need your pattern to account for other whitespace like Cr and Lf, your pattern could look like this:

set @match = '%[ '+char(13)+char(10)+'][1-9][a-z]%';

Upvotes: 0

Mikuana
Mikuana

Reputation: 634

I agree with the comments above; this is a regex problem that needs to be solved with regex tools. I would recommend the assembly created by SimpleTalk. You can get their code and read their very thorough article.

Unfortunately this solution requires serious admin rights to the database and server, so it won't be portable as a script. But I think these functions are worth any effort it takes to get the installed if you develop on the same database on a regular basis.

Just be forwarned, regex is a real processing hog and undermines much of the indexing efficiency in SQL Server. Use it only when you can't use like.

Upvotes: 0

How 'bout a Fresca
How 'bout a Fresca

Reputation: 2317

Try changing your match variable/criteria to this:

SET @match  =  '%[1-9][a-z]%[/][a-z]%'

This will get you the desired result. Loosely translated it is saying "Get me the starting position of the first match where the pattern is [anything]-[number from 1-9]-[single letter from a-z]-[anything]-[slash]-[single letter from a-z]-[anything].
Hope this helps!

Upvotes: 1

Related Questions