SQL wildcard to fetch numbers only-CodePudding

I have a query which is like this
select * from table_sports where player_id like 'playerId-%';

Here there the player_id column can have values in this format playerId-123 or playerId-12-32343 the number size after the hyphen (-) is unknown it can be playerId-1000012 too.

I just want to fetch the records having this format like this playerId-123 and avoid records having this format playerId-12-32343 (two hyphens)

I have tried queries like these select * from table_sports where player_id like 'playerId-[0-9]'; ,select * from table_sports where player_id like 'playerId-%^/-'; That did not work (no records are returned), I googled it , but no luck with that.

Please help.

CodePudding user response：

LIKE doesn't support regular expressions in SQL.

You either need to use the standard compliant similar to or Postgres' proprietary ~ operator to compare against a regex.

where player_id similar to 'playerId-[0-9] '

where player_id ~ '^playerId-[0-9] $'

Note the difference in the regex: similar to doesn't need the "anchor" because it always does a full match.

If you want a case insensitive comparison, use ~* instead of ~

CodePudding user response：

LIKE doesn't work with regular expressions, you can use one of the below methods:

Method 1: Exclude not needed records with another where condition

select * from table_sports 
where player_id like 'playerId-%'
and player_id not like 'playerId-%-%';

Method 2: Use the regular expression operator ~ to use bracket expressions

select * from table_sports 
where player_id ~ '^playerId-([0-9] )$'

You can see this answer to know why LIKE is faster than using regular expressions in this case. And why you shouldn't use SIMILAR TO