0

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.

Deepak velu
  • 154
  • 9

2 Answers2

1

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 ~

  • Awesome that works like a charm!, also one question which way is the fastest in SQL is it similar to or regex? – Deepak velu Mar 11 '22 at 10:43
  • 1
    @Deepakvelu: they should have the same performance (and neither will use an index on that column) –  Mar 11 '22 at 10:53
1

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

Shivam Sharma
  • 1,277
  • 15
  • 31