How to make a SQL query partial match with substrings not in the beginning

Let’s say there is a column called: m_report_notation

Said column, has values such as: NM_004004.5:c.235del (p.L79Cfs*3)

And I would like that, if the value of v is, for example: 235del, then get NM_004004.5:c.235del (p.L79Cfs*3) as a result.

This is my current query and what I’ve tried so far (using MariaDB connector for Django):

    sql="SELECT a.*, COALESCE( NULLIF(a.c_ref_gene, '.'), NULLIF(a.gd_ref_gene, '.') ) AS variant, b.*, c.* FROM `db-dummy`.s_g_data c JOIN `db-dummy`.g_info a ON a.record_id = c.g_id JOIN `db-dummy`.s_data b ON b.record_id = c.s_id WHERE a.gene_name LIKE concat(?, '%') AND a.m_report_notation LIKE concat(?, '%') AND b.s_id LIKE concat(?, '%') ORDER BY a.gene_name LIMIT 30;"
    data = (g, v, s)
    cursor.execute(sql, data)

​x
 
    sql="SELECT a.*, COALESCE( NULLIF(a.c_ref_gene, '.'), NULLIF(a.gd_ref_gene, '.') ) AS variant, b.*, c.* FROM `db-dummy`.s_g_data c JOIN `db-dummy`.g_info a ON a.record_id = c.g_id JOIN `db-dummy`.s_data b ON b.record_id = c.s_id WHERE a.gene_name LIKE concat(?, '%') AND a.m_report_notation LIKE concat(?, '%') AND b.s_id LIKE concat(?, '%') ORDER BY a.gene_name LIMIT 30;"    data = (g, v, s)    cursor.execute(sql, data)​

If for example, v = NM_00 I will get a list of results starting with NM_00, but what if the substring that I’m introducing it’s not in the beginning? What if is something like “235del”?

Answer

Are you looking for like?

where m_report_notation like concat('%', ?, '%')

 
where m_report_notation like concat('%', ?, '%')​

Advertisement

Answer