How to shorten SQL LIKE statement [duplicate]

Question

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.

The short answer is (unfortunately) no. However, there are some insightful questions in the duplicate I linked, please take a look. — Martin
– Martin, Commented Dec 6, 2019 at 11:26
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
– Thorsten Kettner, Commented Dec 6, 2019 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
– J. Doe, Commented Dec 6, 2019 at 11:46
@J.Doe: of course "DBMS" products support regular expressions. However with different levels of completeness - which one are you using? — user330315
– user330315, Commented Dec 6, 2019 at 12:14

Gordon Linoff · Accepted Answer · 2019-12-06 11:30:14Z

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.

answered Dec 6, 2019 at 11:30

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

J. Doe Over a year ago

Yes, I also experienced a lower performance (in time) when used a similar statement with SUBSTRING

RazorKillBen · Accepted Answer · 2019-12-06 11:33:29Z

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.

answered Dec 6, 2019 at 11:33

RazorKillBen

5734 silver badges26 bronze badges

Collectives™ on Stack Overflow

How to shorten SQL LIKE statement [duplicate]

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related