REGEXP_SUBSTR equivalent in MSSQL



  • I migrate from Oracle DB to Postgres/MS-SQL and I want to use REGEXP_SUBSTR equivalent with these databases. I need to rewrite this statement to these databases:

    SELECT b.id,
           b.name,
           b.description,
           REGEXP_SUBSTR(b.description, '(st|ek)', 1, 1, NULL, 1) substring
    FROM books b;
    

    I was able to write this statement for PostgreSQL as:

    SELECT b.id,
           b.name,
           b.description,
           (SELECT array_to_string(a, '') FROM REGEXP_MATCHES(b.description, '(st|ek) ', 'g') AS f(a) LIMIT 1) substring
    FROM books b;
    

    However I have no idea how to rewrite to MS-SQL db. I tried to used statement below however bad result is returned for substring column. Is any solution for this issue please?

     SELECT b.id,
               b.name,
               b.description,
               (SELECT SUBSTRING(b.description, PATINDEX('(st|ek)', CAST(b.description AS varchar)), LEN(b.description))) substring
     FROM books b;
    



  • The solution for Postgres is to use substring()

    substring('one two three four five' from '(\S+)')
    

    yields one. The biggest difference to Oracle's regexp_substring() is that you can't locate the "nth" occurance directly. To get the second word, you could use:

    substring('one two three four five' from '\S+\s*(\S+)')
    

    If you are trying to access words (separated by whitespace), then splitting the string into an array is another alternative:

    (regexp_split_to_array('one two three four five', '\s+'))[1]
    

    The above returns the first word, [2] would return the second. The regex defines the separator (delimiter) between each element.


    Note that Postgres 15 will have a regexp_substr() that is pretty much identical to Oracle's implementation.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2