CODEWITHSUNDEEP
sql
user1745356
user1745356

Reputation: 4673

sql query: If a field is a prefix of a key

I have a few records in my table. I want to get all records where field "x" is a prefix of some key. In other words say i have 3 records where field "x" in first record is abc, in second abcd, in third xyz. And I want to get all records where x is a prefix of abcdefg.

    |--------x--------|
    |_________________|
   1|-----abc---------|
   2|-----abcd--------|
   3|-----xyz---------|

I want to select records where value of field x is a prefix of abcdefg, I can write something like 

select *
from table
where x in (a, ab, abc, abcd, abcde, abcdef, abcdefg)

But is there a better solution

Upvotes: 0

Views: 567

Answers (2)

Alex
Alex

Reputation: 35409

Using MS SQL Server:

select * from [tablex] where left([x], 1) = 'a' and charindex([x], 'abcdefg') = 1

Or just:

select * from [tablex] where charindex([x], 'abcdefg') = 1

http://sqlfiddle.com/#!3/111a3/1

Upvotes: 1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

For Postgres (this is standard SQL):

WHERE 'abcdefg' LIKE x || '%'

For Oracle, MySQL, Postgres:

WHERE 'abcdefg' LIKE CONCAT(x,'%')

For SQL-Server:

WHERE 'abcdefg' LIKE x + '%'

But what you have, i.e.:

WHERE x IN ('', 'a', 'ab', 'abc', 'abcd', 'abcde', 'abcdef', 'abcdefg')

is probably the most efficient solution and should work in almost all DBMS.

Upvotes: 1

Related Questions