Is it possible to query a comma separated column for a specific value?

by

You can, using LIKE. You don’t want to match for partial values, so you’ll have to include the commas in your search. That also means that you’ll have to provide an extra comma to search for values at the beginning or end of your text:

select 
  * 
from
  YourTable 
where 
  ',' || CommaSeparatedValueColumn || ',' LIKE '%,SearchValue,%'

But this query will be slow, as will all queries using LIKE, especially with a leading wildcard.

And there’s always a risk. If there are spaces around the values, or values can contain commas themselves in which case they are surrounded by quotes (like in csv files), this query won’t work and you’ll have to add even more logic, slowing down your query even more.

A better solution would be to add a child table for these categories. Or rather even a separate table for the catagories, and a table that cross links them to YourTable.

More Related Contents:

  1. How do I spool to a CSV formatted file using SQLPLUS?
  2. ORA-00904: invalid identifier
  3. Using an Alias in a WHERE clause
  4. Using ‘case expression column’ in where clause
  5. How to use BOOLEAN type in SELECT statement
  6. ORA 00904 Error:Invalid Identifier
  7. How to use Alias in Where clause? [duplicate]
  8. how to save sql query result to csv in pandas
  9. Update statement with inner join on Oracle
  10. Splitting string into multiple rows in Oracle
  11. Is there any function in oracle similar to group_concat in mysql? [duplicate]
  12. How to use the ‘as’ keyword to alias a table in Oracle?
  13. Can a foreign key be NULL and/or duplicate?
  14. What is the string concatenation operator in Oracle?
  15. How can I introduce multiple conditions in LIKE operator?
  16. Is there any boolean type in Oracle databases?
  17. SQL (ORACLE): ORDER BY and LIMIT [duplicate]
  18. Dynamic Oracle Pivot_In_Clause
  19. Convert Unixtime to Datetime SQL (Oracle)
  20. What is the equivalent PostgreSQL syntax to Oracle’s CONNECT BY … START WITH?
  21. How to debug ORA-01775: looping chain of synonyms?
  22. Generating dates between two dates
  23. SQL Command not properly ended?
  24. What does the colon sign “:” do in a SQL query?
  25. How do I get textual contents from BLOB in Oracle SQL
  26. Inserting into Oracle and retrieving the generated sequence ID
  27. adding primary key to sql view
  28. Oracle DB: How can I write query ignoring case?
  29. ORA-00942: table or view does not exist (works when a separate sql, but does not work inside a oracle function)
  30. Oracle Convert TIMESTAMP with Timezone to DATE

Leave a Comment