How would I match a name, such that one of the middle letters of the name must come from the word ‘qwerty'(thus containing either of the letters ‘q’, ‘w’ , ‘e’, ‘r’, ‘t’,’y’)? I am curious how you can determine what the middle lette…
Tag: pattern-matching
jsonb LIKE query on nested objects in an array
My JSON data looks like this: given a text “foo” I want to return all the tuples that have this substring. But I cannot figure out how to write the query for the same. I followed this related answer but cannot figure out how to do LIKE. This is what I have working right now: Instead of passing the…
Get column names which do not match a particular ending string
I have a table with a structure like this: abc_col | abcd | ab_col | | | | Some column names end in _col and some do not. abc or ab or abcd are random column name …
How to use % operator from the extension pg_trgm?
I have my pg_trgm module installed. The schema set is extensions. To use it I have to run something like this select: I’m trying to run a statement using the % operator and got the following message. What is necessary to run % or <-> operators? Answer Most probably the problem is with the search_p…
Regex remove all occurrences of multiple characters in a string
In my PostgreSQL I want to replace all characters (;) occurrences in a string. My query: update table_name set text = regexp_replace(text, ‘/[(;)]+/g’, ”); I think my regexp is …
Text to List in SQL
Is there any way on how to convert a comma separated text value to a list so that I can use it with ‘IN’ in SQL? I used PostgreSQL for this one. Ex.: This query: produces ‘SG’,’PH’ I wanted to produce this query: Nothing returned when I executed the first query. The table h…
Does PostgreSQL support “accent insensitive” collations?
In Microsoft SQL Server, it’s possible to specify an “accent insensitive” collation (for a database, table or column), which means that it’s possible for a query like to find a row with a Joao name. I know that it’s possible to strip accents from strings in PostgreSQL using the u…
What’s the R equivalent of SQL’s LIKE ‘description%’ statement?
Not sure how else to ask this but, I want to search for a term within several string elements. Here’s what my code looks like (but wrong): des is a vector that stores strings such as “Swinging Strike”, “In play (run(s))”, “In play (out(s) recorded)” and etc. What I wa…