PSQL: Concatenate a value with LIKE in stored procedure

Question

I am trying to concatenate a variable using the LIKE statement and for some reason it only finds values where the word to search is at the end of the text variable.

I am using PostgreSQL 8.4 and this is stored in a function (stored procedure)

considering in this example:

a.key1 is "HELLO"

a_text is "I SAY HELLO TO THE WORLD"

Code:

SELECT count(1), a.key1, a.active, a.campkeydbid
    FROM campkeydb a
WHERE a_text LIKE '%'|| a.key1 ||'%'
   GROUP BY a.key1, a.active, a.campkeydbid
INTO a_count, a_campaignkey, a_active, a_campkeydbid;

In this stored procedure it will NOT return the values; it will not find the word "HELLO"?

It will ONLY return the values if a_text contains "I SAY HELLO"

Does anyone knows what I am doing wrong? It seems that it is correct as I am concatenating a % on both sides of the variable a.key1.

Are you certain that a.key1 is exactly 'HELLO' rather than ' HELLO' or something similar? Try a quick select '-' || key1 || '-' from campkeydb to see if you have stray spaces. — mu is too short
– mu is too short, Commented Jul 6, 2012 at 3:53
Or perhaps there's something wrong with the a_text value? Have you tried running this query with a_text replaced with the string itself? — Andriy M
– Andriy M, Commented Jul 6, 2012 at 4:58
Thank you for your response.. I did some quick selects and no spaces, but I also added trim on my JAVA to send "clean" info ! thanks... it was solved by adding "AND a.deleted = false", I realized that there were more rows but were marked deleted... — Beto
– Beto, Commented Jul 6, 2012 at 15:50

Bob Folkerts · Accepted Answer · 2012-07-06 06:41:39Z

1

You can use the position string function instead of like. Here is a sample query using a table of the regions and departments of France. I'll try and find all the departments that have a name that includes the region name.

select r.name as region, d.name as deprtment, position( r.name in d.name) as pos
from regions r
join departments d on d.region = r.code
where position( r.name in d.name) != 0
and r.name != d.name;

The results are

region  department     pos
"Corse" "Corse-du-Sud"  1
"Corse" "Haute-Corse"   7

I added the pos column to show that strings are indexed from 1, not 0. I tried the same thing with 'like' (both queries have the same query plan and should give the same performance):

select r.name as region, d.name as deprtment, position( r.name in d.name) as pos
from regions r
join departments d on d.region = r.code
where d.name like '%' || r.name || '%'
and r.name != d.name;

I like the appearance of the first query, but they both do the same thing. So your logic seems correct, so this seems like a typo in a string.

answered Jul 6, 2012 at 6:41

Bob Folkerts

3762 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Beto Over a year ago

Thank you for all the responses and help... I used both select statements and you are right the logic was correct (thank you for making sure I was not going insane) looking at my data, I saw that there were more rows with the same key but they were marked "deleted" so I added (AND a.deleted = FALSE) and this solved the problem... it seems that it is always human error... thanks again for your time and help!!

Collectives™ on Stack Overflow

PSQL: Concatenate a value with LIKE in stored procedure

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related