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 ~