Hi Andrus,
2010/10/13 Andrus
> 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 se
2010/10/13 Andrus :
> 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
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:
gj=
2010/10/13 Andrus :
> 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
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,'-',''),'
'
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
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 :
> CHAR(20) c
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 exampl
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 retur
2010/10/13 Andrus :
> 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
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
CRET
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 m
12 matches
Mail list logo
