CODEWITHSUNDEEP
sqlsql-likestartswith
Chandan Hasija
Chandan Hasija

Reputation: 35

select TableData where ColumnData start with list of strings

Following is the query to select column data from table, where column data starts with a OR b OR c. But the answer i am looking for is to Select data which starts with List of Strings.

SELECT *  FROM Table WHERE Name LIKE '[abc]%'

But i want something like

SELECT *  FROM Table WHERE Name LIKE '[ab,ac,ad,ae]%'

Can anybody suggest what is the best way of selecting column data which starts with list of String, I don't want to use OR operator, List of strings specifically.

Upvotes: 0

Views: 2049

Answers (2)

Mureinik
Mureinik

Reputation: 311188

To add to Tim's answer, another approach could be to join your table with a sub-query of those values:

SELECT *
FROM   mytable t
JOIN   (SELECT 'ab' AS value
        UNION ALL
        SELECT 'ac'
        UNION ALL
        SELECT 'ad'
        UNION ALL
        SELECT 'ae') v ON t.vame LIKE v.value || '%'

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

The most general solution you would have to use is this:

SELECT *
FROM Table
WHERE Name LIKE 'ab%' OR Name LIKE 'ac%' OR Name LIKE 'ad%' OR Name LIKE 'ae%';

However, certain databases offer some regex support which you might be able to use. For example, in SQL Server you could write:

SELECT *
FROM Table
WHERE NAME LIKE 'a[bcde]%';

MySQL has a REGEXP operator which supports regex LIKE operations, and you could write:

SELECT *
FROM Table
WHERE NAME REGEXP '^a[bcde]';

Oracle and Postgres also have regex like support.

Upvotes: 1

Related Questions