Translating regex into LIKE statements for SQL Server-CodePudding

I have a regular expression that I have been using in Postgres that I want to translate to SQL Server, but I believe SQL Server lacks the full capabilities of using regular expressions. However, I think my regular expression should be simple enough to translate into LIKE statements.

This is my regular expression: ^[123ABC]*([456]). From my understanding of regular expressions, the two features that I'm using here are the ability to match zero-or-more characters at the beginning of the string and capture groups.

Here's an example using Postgres:

CREATE TABLE inventory (id INT, pcode VARCHAR(10));
INSERT INTO inventory VALUES (1, 'AABC547');
INSERT INTO inventory VALUES (2, '656ABC49');
INSERT INTO inventory VALUES (3, '11AB727');
INSERT INTO inventory VALUES (4, '7712346');

SELECT
    id,
    pcode,
    COALESCE(
        SUBSTRING(pcode from '^[123ABC]*([456])'):: int, 0
    ) AS desired_val
FROM inventory;

-- id   pcode    desired_val
-- 1    AABC547  5
-- 2    656ABC49 6
-- 3    11AB727  0
-- 4    7712346  0

Here's my attempt to try to rewrite this for use with SQL Server which gets the first row wrong because I am not getting the first value that matches my desired pattern [456], but the last one.

SELECT
    id,
    pcode,
    CASE
        WHEN pcode LIKE '[456]%' THEN CAST(SUBSTRING(pcode, 1, 1) AS INT)
        WHEN pcode LIKE '[123ABC]%4%' THEN 4
        WHEN pcode LIKE '[123ABC]%5%' THEN 5
        WHEN pcode LIKE '[123ABC]%6%' THEN 6
        ELSE 0
    END AS desired_val
FROM inventory;

-- id   pcode    desired_val
-- 1    AABC547  4
-- 2    656ABC49 6
-- 3    11AB727  0
-- 4    7712346  0

CodePudding user response：

If you really have to do this with pure T-SQL, you may write something like this:

SELECT
  id,
  pcode,
  CASE
    WHEN pcode LIKE '[456]%' THEN CAST(SUBSTRING(pcode, 1, 1) AS INT)
    WHEN pcode LIKE REPLICATE('[123ABC]', CHARINDEX('4', pcode) - 1)   '4%' THEN 4
    WHEN pcode LIKE REPLICATE('[123ABC]', CHARINDEX('5', pcode) - 1)   '5%' THEN 5
    WHEN pcode LIKE REPLICATE('[123ABC]', CHARINDEX('6', pcode) - 1)   '6%' THEN 6
    ELSE 0
  END AS desired_val
FROM inventory;

The trick is to use CHARINDEX() to get the position of the first occurrence of a [456] char, and then use REPLICATE() to repeat the [123ABC] pattern a number of times equal to the number of characters that precede the desired character. So, in the first example, where the '5' char is found at the fifth position, the constructed pattern will be:

[123ABC][123ABC][123ABC][123ABC]5%