I have a Postgres table with two columns (in fact it has many more), a numeric column named TicketId and a text column named Tags. Here are three sample rows of data:
TicketId Tags
1234 apple,apple_pie,apricot,peach,rhubarb_crumbs,rhubarb
5678 apple,apricot,peach,rhubarb_crumbs,rhubarb,wheat
9999 rhubarb,turnips,whey
FYI, the front-end website is a out of the box product that I have very little control over. As data is saved, keywords are extracted and saved as Tags, thus the need to query the tag column. The system automatically puts the comma in between each tag.
When the table is created, the column is text.
Tags text not null,
In the above example, I only want to return rows with 'rhubarb'. I don't want any rows with rhubarb_crumbs.
I'm trying to execute this query to only pull rows where Tags contains 'rhubarb'. Note that when 'rhubarb' appears at the front of the string there is no leading comma and when 'rhubarb' appears at the end of the string there is no trailing comma.
SELECT TicketId, ',' || Tags || ',' AS Tags
FROM [tablename]
WHERE ',' || Tags || ',' LIKE '%,rhubarb,%'
The query runs, but only returns the 2nd row. The output looks like this. Note the comma in the first and last positions of the text field:
TicketId Tags
5678 ,apple,apricot,peach,rhubarb_crumbs,rhubarb,wheat,
Is it possible to put delimiters on the ends of a text field and query it?
I could do the following query
WHERE Tags LIKE '%rhubarb%'
OR Tags LIKE 'rhubarb,%'
OR Tags LIKE '%,rhubarb'
to pull all three rows, but if I have a request for multiple ingredients, then the WHERE LIKE statement becomes very cumbersome. I'm sure there are performance issues for each additional LIKE statement within the query.
Thus the desire to somehow put a leading and trailing comma around the Tags column when I run the query.
I could do a SELECT FROM .. INSERT INTO ... a temporary table to get the commas around the Tags column, but that seems like a LOT of work to me for something that should be very simple.
Thank you!!!
Just use begin line (^) and end line ($) match symbols
in your example, if the tag field starts with the word you need to find, the query will not work. And if the tag field contains only the word you need to find, the query will also not work.
pattern(^|,)rhubarb(,|$)
select 'apple,apple_pie,apricot,peach,rhubarb_crumbs,rhubarb' ~ '(^|,)rhubarb(,|$)';
?column?
----------
t
select 'apple,apple_pie,apricot,peach,rhubarb_crumbs,rhubarb,test2' ~ '(^|,)rhubarb(,|$)';
?column?
----------
t
select 'rhubarb,asdfasdf' ~ '(^|,)rhubarb(,|$)';
?column?
----------
t
select 'rhubarb' ~ '(^|,)rhubarb(,|$)';
?column?
----------
t
SELECT TicketId FROM [tablename] WHERE Tags ~ '(^|,)rhubarb(,|$)'