T-SQL to Postgres wildcard logic

Question

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!

postgresql.org/docs/current/…

user330315
– user330315

2021-10-25 18:55:59 +00:00
Commented Oct 25, 2021 at 18:55 — user330315
– user330315, Commented Oct 25, 2021 at 18:55

paneerakbari · Accepted Answer · 2021-10-25 19:11:44Z

3

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:

edited Oct 25, 2021 at 19:11

user330315

answered Oct 25, 2021 at 18:59

paneerakbari

7451 gold badge5 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user330315 Over a year ago

Just as a side note: the SQL standard only defines the wildcards % and _ for the LIKE operator and nothing else. This is not specific to Postgres. SQL Server's support for "regex like" wildcards in T-SQL is non-standard

paneerakbari Over a year ago

@a_horse_with_no_name That's an important note! Thank you for that and the edit for the current doc URL (I wasn't watching closely)

Collectives™ on Stack Overflow

T-SQL to Postgres wildcard logic

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related