Hello
you can use a own function
CREATE OR REPLACE FUNCTION clean_some(text)
RETURNS text AS $$
SELECT replace(replace($1, ' ',''),'-','')
$$ LANGUAGE sql;
then you can do query with where clause
WHERE clean_some(colum) = clean_some('userinput');
you can enhance it with functional index
2010/10/13 Andrus kobrule...@hot.ee:
CHAR(20) columns in 8.4 database may contains spaces and - signs like
13-333-333
12 3-44
33 33 333
12345
User enters code to search without spaces and - signs, like 12344
How to search for product code ignoring spaces and - signs?
For example
Andrus wrote:
CHAR(20) columns in 8.4 database may contains spaces and - signs like
13-333-333
12 3-44
33 33 333
12345
User enters code to search without spaces and - signs, like 12344
How to search for product code ignoring spaces and - signs?
For example searching for code 12344 should
On 14/10/10 01:45, Andrus wrote:
CHAR(20) columns in 8.4 database may contains spaces and - signs like
13-333-333
12 3-44
33 33 333
12345
User enters code to search without spaces and - signs, like 12344
How to search for product code ignoring spaces and - signs?
For example
Dear Andrus,
Quick Dirty Soln:
SELECT * from table where regexp_replace( col , '[-\\s+]' , '' ,
'g') ilike '%search_term%' ;
note above sql will not use any index if you have to search 1s of
rows use alternate
approaches.
regds
Rajesh Kumar Mallah.
2010/10/13 Andrus
On 10/13/2010 07:45 PM, Andrus wrote:
CHAR(20) columns in 8.4 database may contains spaces and - signs like
13-333-333
12 3-44
33 33 333
12345
User enters code to search without spaces and - signs, like 12344
How to search for product code ignoring spaces and - signs?
There are many options
Hello,
For example searching for code 12344 should return
12 3-44 as matching item.
Andrus.
This will do?
postgres=# select name from myt;
name
13-333-333
12 3-44
33 33 333
12345
(4 rows)
postgres=# select * from myt where translate(translate(name,'-',''),'
','')
2010/10/13 Andrus kobrule...@hot.ee:
CHAR(20) columns in 8.4 database may contains spaces and - signs like
13-333-333
12 3-44
33 33 333
12345
User enters code to search without spaces and - signs, like 12344
How to search for product code ignoring spaces and - signs?
For example
select regexp_replace(myval, E'(\\D)', '', 'g') from foo;
for added speed, you might consider this:
CREATE INDEX ON foo((regexp_replace(myval, E'(\\D)', '', 'g'))::bigint);
which is also going to protect you against inserts where value doesn't
contain any digits.
and added benefit of index:
2010/10/13 Andrus kobrule...@hot.ee:
CHAR(20) columns in 8.4 database may contains spaces and - signs like
13-333-333
12 3-44
33 33 333
12345
User enters code to search without spaces and - signs, like 12344
How to search for product code ignoring spaces and - signs?
For example
Hi Andrus,
2010/10/13 Andrus kobrule...@hot.ee
CHAR(20) columns in 8.4 database may contains spaces and - signs like
13-333-333
12 3-44
33 33 333
12345
User enters code to search without spaces and - signs, like 12344
How to search for product code ignoring spaces and - signs?
For
CHAR(20) columns in 8.4 database may contains spaces and - signs like
13-333-333
12 3-44
33 33 333
12345
User enters code to search without spaces and - signs, like 12344
How to search for product code ignoring spaces and - signs?
For example searching for code 12344 should return
12 3-44 as
12 matches
Mail list logo