T-SQL to Postgres wildcard logic-CodePudding

I am in the process of converting some t-sql queries to postgres and I am having trouble wrapping my head around the postgres wildcard logic.

eg:

The following query in tsql will yeild 'A' however in postgres it returns 'B'

    Select 
    case when 'abcd 1234' like '%[a-z]%[0-9]%' then 'A' else 'B' end as Q1

What would be the postgres equivalent to the above case when statement? Furthermore, does anyone have a general rule of thumb for converting tsql string logic to postgres ?

Thanks in advance!

CodePudding user response：

The difference that you're running into here is that SQL Server's TSQL accepts character range wildcards through the square bracket [] syntax but PostgreSQL does not.

Instead, PostgreSQL incorporates support for POSIX regular expressions within a query using the RegEx match operators - variations of ~ - in place of LIKE and offer quite a bit of flexibility with respect to case sensitivity and string-matching.

Restating your original query in a POSIX RegEx syntax to achieve an output of 'A' will resemble this:

  Select 
    case when 'abcd 1234' ~ '(.*)[a-z](.*)[0-9](.*)' then 'A' 
    else 'B' end as Q1

As for the notion of general heuristics for handling these sorts of conversions, I would suggest that T-SQL character-set wildcards should be implemented as POSIX regular expressions using the RegEx match operator rather than LIKE. Otherwise, the T-SQL % and _ wildcards behave equivalently to the same PostgreSQL wildcards.

References: