SQL LIKE (but in reverse?)

Posted By: DoomBringer | Sep 11th, 2007 @ 3:48 PM
page 1 of 1
Comments: 6 | Views: 7278
#Sep 11th, 2007 @ 3:48 PM
DoomBringer
DoomBringer
Doom!

In SQL, I know you can do something like this:

select * from tbl where col1 like %foo%

and it will return any results that have "foo" inside of them, for instance, myfoo would be returned if it was in the table.  What I want to do is a bit the other direction.  Is there an easy way to input "myfoo" and get "foo" out (assuming "foo" is a value in the table)?  More specifically, I want a count of hits, but that is easy enough to add on after the query is there.

Some other notes: col1 is the primary key, so in the end I'm only looking for a 0 or 1.

The end goal is to find out if the inputted parameter, "myfoo" contains a word from the table.  What I was thinking might be possible is using a temporary table to hold "myfoo" and then using a WHERE-IN clause and a nested query:

select * from temptbl where col1 in (select * from tbl)

But then I realized that WHERE-IN probably doesn't support LIKE.

#Sep 11th, 2007 @ 4:25 PM
Matthew van Eerde
Matthew van Eerde
AKA Maurits
where 'myfoo' like '%' + col1 + '%'
-- returns rows where col1 is any of:
-- ''
-- m
-- y
-- f
-- o
-- my
-- yf
-- fo
-- oo
-- myf
-- yfo
-- foo
-- myfo
-- yfoo
-- myfoo
#Sep 11th, 2007 @ 4:35 PM
DoomBringer
DoomBringer
Doom!
Cool -- I'll try that out.  I'm using pgsql (not my idea, mind you) and this works perfectly:
select count(*) from tbl where strpos('myfoo',col1) > 0
#Sep 11th, 2007 @ 4:41 PM
DoomBringer
DoomBringer
Doom!
Matthew van Eerde wrote:
where 'myfoo' like '%' + col1 + '%'
-- returns rows where col1 is any of:
-- ''
-- m
-- y
-- f
-- o
-- my
-- yf
-- fo
-- oo
-- myf
-- yfo
-- foo
-- myfo
-- yfoo
-- myfoo

This didn't seem to work on my live pgsql database.  It seems confused over the + operator in the like clause:
ERROR: operator does not exist: "unknown" + character varying
SQL state: 42883
Hint: No operator matches the given name and argument type(s). You may need to add explicit type casts.

Edit: Though if this works on MS SQL, perfect, because I will have to port this to work on MS SQL and Oracle at some point.
#Sep 12th, 2007 @ 12:57 PM
Matthew van Eerde
Matthew van Eerde
AKA Maurits
Maybe there's a different operator for concatenating strings in postgreSQL?

Or could you pre-add the %s in the parameter?
#Sep 12th, 2007 @ 7:10 PM
DoomBringer
DoomBringer
Doom!
Matthew van Eerde wrote:
Maybe there's a different operator for concatenating strings in postgreSQL?

Or could you pre-add the %s in the parameter?

Actually, I think pgsql does have a different cat operator.  || is the operator for it.  (Weird I know.)

Anyhow, I'll keep this in mind for when I need to do this for Oracle and MSSQL. 
#Sep 13th, 2007 @ 8:41 AM
Yggdrasil
Yggdrasil
Pour me a cab, 'cause I can't drink no more.
DoomBringer wrote:
Actually, I think pgsql does have a different cat operator.  || is the operator for it.  (Weird I know.)

Anyhow, I'll keep this in mind for when I need to do this for Oracle and MSSQL. 


Actually,  I think Oracle uses || as well. Vague recollections.
page 1 of 1
Comments: 6 | Views: 7278