-1

I am trying to check if a given parameter in a SP is eligible to be used and I have the following REGEX:

[a-zA-Z][a-zA-Z]\d{4}|[a-hA-H]\d{3}|[j-mqrtvwzJ-MQRTVWZ]\d{4}

It supposed to find a match for entries like xx9999 and x999. I tested it in https://regex101.com/ and it works fine.

However I am not managing to make it work in TSQL. My test code:

declare @param varchar(max) = 'xx1234'

-- try 1
select 'match' where @param like '[a-zA-Z][a-zA-Z]\d{4}|[a-hA-H]\d{3}|[j-mqrtvwzJ-MQRTVWZ]\d{4}'

-- try 2
select patindex('[a-zA-Z][a-zA-Z]\d{4}|[a-hA-H]\d{3}|[j-mqrtvwzJ-MQRTVWZ]\d{4}',@param)

Any idea?

Yan Kleber
  • 407
  • 2
  • 4
  • 11

2 Answers2

0

In case of someone be in the same shoes I figured the issue and came with the fix.

What was puzzling me is that I had used successfully PATINDEX with REGEX before for a similar check so I didn't know why this one was not working.

Long story short I noticed that for some reason TSQL will only recognize very simply REGEX and the one I was trying to use was kind of too much 'complex' (although it's correct).

I ended realizing that the problem is with the \d{n} and the upright slash (OR operator). To fix it I replaced the \d{n} with multiple [0-9] and 'broke' the REGEX in three subsequent tests like this:

DECLARE @param varchar(max) = 'ab1234'

IF PATINDEX('[a-zA-Z][a-zA-Z][0-9][0-9][0-9][0-9]', @param) = 0
AND PATINDEX('[a-hA-H][0-9][0-9][0-9]', @param) = 0
AND PATINDEX('[j-mqrtvwzJ-MQRTVWZ][0-9][0-9][0-9][0-9]', @param) = 0
SELECT '0'
ELSE
SELECT '1'

It worked as supposed.

Yan Kleber
  • 407
  • 2
  • 4
  • 11
  • 1
    https://learn.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql?view=sql-server-ver15 As you have now realized, REGEX doesn't really work in SQL land, but here is the microsoft tech doc on LIKE that shows the limited capability it does have. In my experience if the regex is too complicated, I offload the data into a language that can handle it (.NET). – Anthony Hancock Mar 11 '21 at 14:29
  • 1
    You can combine them into a [`case`](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql?view=sql-server-ver15) expression, if that helps: `case when @param like '[a-zA-Z][a-zA-Z][0-9][0-9][0-9][0-9]' then 1 when @param like '[a-hA-H][0-9][0-9][0-9]' then 1 when @param like '[j-mqrtvwzJ-MQRTVWZ][0-9][0-9][0-9][0-9]' then 1 else 0 end`. Or you can return strings as shown in your code, e.g. `'1'` rather than `1`. – HABO Mar 11 '21 at 19:07
  • Anthony, I got your point and I COULD do that (in fact the SP is called by VB code). I didn't though because the tested param is text typed into an autocompletebox for car part search in a website. The purpose of the test is to check what type of query to run: it may be to search part by description or by two alternative code format then run one of three different queries in the same SP. Because the part description I already have to deal with 6 other parameters (7 total) so if I'd REGEX user typed text in VB I'd have to add an extra param to the SP to act as a flag and I didn't want this... – Yan Kleber Mar 12 '21 at 14:59
  • Thank you HABO this is a great approach too! – Yan Kleber Mar 12 '21 at 15:25
-1
DECLARE @param varchar(max) = 'xx1234'

SELECT CASE WHEN @param LIKE '[a-zA-Z]%[1-9]' THEN 1 ELSE 0 END
Amira Bedhiafi
  • 8,088
  • 6
  • 24
  • 60
  • Other than leaving out most of the details of the pattern matching, it also excludes any string that ends with `0`. – HABO Mar 11 '21 at 18:58