Thread: string filtering in postgres?

I need to normalize a column for search purposes by stripping all
non-alphanumeric characters:

   UPDATE my-table SET id_stripped = ??? id;

I have been playing with regexp_replace( id, ????,'');

   UPDATE my-table
        SET id_stripped = regexp_replace( id, <various>,'');id;

Without much luck. Can this even be done with regex, or should I just
write a custom sql function?

kt

Kenneth Tilton <kentilton@gmail.com> writes:
> I need to normalize a column for search purposes by stripping all
> non-alphanumeric characters:
> Without much luck. Can this even be done with regex, or should I just
> write a custom sql function?

Perhaps along the lines of

regression=# select regexp_replace('ABC!$56xyz, %', '[^A-Za-z0-9]', '', 'g');
 regexp_replace
----------------
 ABC56xyz
(1 row)

This isn't going to be tremendously efficient for very long strings,
but I doubt you could do much better without resorting to a C function.

            regards, tom lane