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.

>Solution :

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 ~