2

How could I shorten the following sql query (where I return rows which start with one of four different strings in column "mycolumn"):

select * from mytable where mycolumn LIKE 'xyz%' 
OR mycolumn LIKE 'abc%' 
OR mycolumn LIKE 'def%' 
OR mycolumn LIKE 'gth%'

J.

J. Doe
  • 37
  • 4
  • 2
    The short answer is (unfortunately) _no_. However, there are some insightful questions in the duplicate I linked, please take a look. – Martin Dec 06 '19 at 11:26
  • 1
    What database are you using? – Gordon Linoff Dec 06 '19 at 11:28
  • Why shorten it? I think the query looks fine as is. If your DBMS supports regular expressions, you may be able to use a single expression in your `WHERE` clause. – Thorsten Kettner Dec 06 '19 at 11:30
  • Just looking for a less complicated statement. Reg expressions wont work in DBMS but this LIKE IN idea is helpful, thanks – J. Doe Dec 06 '19 at 11:46
  • @J.Doe: of course "DBMS" products support regular expressions. However with different levels of completeness - which one **are** you using? –  Dec 06 '19 at 12:14

2 Answers2

4

You have some options, although none are necessarily good from a performance perspective. If all the prefixes are the same length, then:

where left(column, 3) in ('xyz', 'abc', 'def', 'ghi')

If you are using a database that supports regular expressions:

where column ~ '^(xyz|abc|def|ghi)'

In this case, ~ is the Postgres syntax for regular expression matching.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Yes, I also experienced a lower performance (in time) when used a similar statement with SUBSTRING – J. Doe Dec 06 '19 at 11:39
0

If your database supports Regex, you could use the below code:

select * from mytable where mycolumn ~ '^(xyz|abc|def|gth)'

Learning Regex is definitely worth considering if your database supports it, although I would imagine the impact performance isn't great, so it'd be recommended if your query is going to rely on a huge volume of strings.

RazorKillBen
  • 561
  • 2
  • 20