Filter one field by another field of the same table

[MilaK]

Hello,

I would like to filter [gene_panel] fields by [gene] field of the same table.

The [gene] field consists on a single gene name (e.g KRAS) and the [gene_panel] field consists of a comma delimited string of genes (KRAS,NRAS,KIT). I would like a query to return records where [gene_panel] contains [gene]. For, example, “KRAS,NRAS,KIT” contains “KRAS”, therefore, this record should be returned.

I’ve tried the following query but it’s returning all records, even when gene_panel doesn’t contain gene.

Here is the SQL:

SELECT tbl_failed_amplicons.gene, tbl_Samples.tumor_type, tbl_Samples.gene_panel
FROM tbl_Samples INNER JOIN tbl_failed_amplicons ON tbl_Samples.sample_id = tbl_failed_amplicons.sample_id
WHERE (((tbl_Samples.gene_panel) Like "*[gene]*"));

Thanks, Mila

 

[plog]

1. Your table structure is improper. Discrete pieces of data should be stored discretely. You shouldn't cram a bunch of information into one field seperated by commas. That means your genepool field should instead become its own table with each value of genepool in its own record.

2. Instead of LIKE in your query, check out the InStr function (https://www.techonthenet.com/oracle/functions/instr.php).

 

[MilaK]

Expression like this: instr(gene_panle, panel) > 0 that will return True or False?

 

[plog]

Yes, that will return true or false.