Thread: ignore single character in SELECT query?

Given select criteria "dont" I would like to return rows with values
"don't".  Or similarily I want rows with "they're" given input criteria
"theyre".

So basically I want to ignore a single character (the apostrophe
character), anywhere in the middle of my search word, in selecting
results.  How can I do this?

jspring@gmail.com writes:

> So basically I want to ignore a single character (the apostrophe
> character), anywhere in the middle of my search word, in selecting
> results.  How can I do this?

WHERE replace(name,'''','') like '%dont%'

Beware of quoting issues if "dont" is coming from user supplied inputs.

-- 
greg

Use
--------
select replace(quote_literal('don\'t'), '\'', '');
Or
select replace(quote_ident(myColumnName, '\'', '');

Thanks
Dinesh Pandey


-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of jspring@gmail.com
Sent: Wednesday, April 13, 2005 8:16 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] ignore single character in SELECT query?

Given select criteria "dont" I would like to return rows with values
"don't".  Or similarily I want rows with "they're" given input criteria
"theyre".

So basically I want to ignore a single character (the apostrophe
character), anywhere in the middle of my search word, in selecting
results.  How can I do this?


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate     subscribe-nomail command to
majordomo@postgresql.orgso that your     message can get through to the mailing list cleanly