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-12-32343 the number size after the hyphen (-) is unknown it can be
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.
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