Re: search and replace possible on SQL?

On Mon, May 06, 2002 at 04:02:23PM +0200,
"Duncan Adams (DNS)" <duncan(dot)adams(at)vcontractor(dot)co(dot)za> wrote:
>
> this would be easy if i could just get the support personal to enter the
> information as it appears in the DNS (they add spaces to make it easier to
> read.), after many hours of head banging i have given this up. I also can't
> just take out the spaces they put in to the data as there are some devices
> (like hubs) that have names with spaces in them.
>
> is there any way of doing this with sql eg.
>
> select * from table1 where name.table1 = <regualer(name)>.table2;

You can do regular expression pattern matching. You could take the string
they enter, escape special characters and then add ? after every space
(assuming all they do is add extra spaces) and do a case insenstive
regular expression match using the modified string.

If the number of hits is a small fraction of a large table, then you might
want to add another column that is a simplified name of the device using
a procedure that will produce the same results for the real name as it
will for typical data entered by your users and still result in unique
names for all devices. Then you can then index this column and use it
to (possibly) speed up searches. A sample function would be to drop all
nonletters, nondigits and replace uppercase by lowercase.