CODEWITHSUNDEEP
sqlitesubstringsql-like
Malonge
Malonge

Reputation: 2040

How to search as subsequence

I would like to search through a database table and return any string that is a subsequence of another string.

When I want to search for a string that is equal to another string, I use the following:

SELECT kmer from data where kmer="ATCG"

Assuming kmer = "ATCG", a match will be made. But what if I wanted to search as a subsequence? Something like the following:

SELECT kmer from data where kmer in "XXATCGXX"

But obviously, this syntax is incorrect. These strings are static and I am not able to use wildcards with the LIKE syntax.

To be clear, any of the following values should return a match: 

X
XX
XAT
XATC
ATC
TC
GX
CGXX
A
T
C
G

Just to name a few examples. These are all subsequences of "XXATCGXX" so can I use wildcats to cover all of these possibilities?

Upvotes: 1

Views: 179

Answers (1)

CL.
CL.

Reputation: 180020

LIKE is typically used with a column value on the left, but as mentioned by Colonel Thirty Two, you can just reverse the comparison:

SELECT kmer FROM data WHERE 'XXATCGXX' LIKE '%' || kmer || '%';

Upvotes: 1

Related Questions