Re: search and replace possible on SQL?

On Tue, 2002-05-07 at 02:02, Duncan Adams (DNS) wrote:
>
> i have two areas where i get information for my project. one is obtained
> from a SNMP walk across our systems and the other is captured by support
> personal.
>
> Now i want to be able to compare a list of systems that the SMNP walk has
> handed over to me with the information that has been data captured by the
> support personal and either update or insert.
>
> 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;

Is there any structure to the transformation you need to make? You
should be able to:

SELECT * FROM t1 WHERE t1.name = myfunction(t2.name);

Then define a function in PL/PgSQL like this:

-- CREATE or REPLACE only available from 7.2 on
CREATE or REPLACE FUNCTION
myfunction( TEXT ) RETURNS TEXT
AS '
DECLARE
original ALIAS FOR $1;
transformed TEXT;
spacepos INT;
BEGIN
transformed := lower(original);
IF transformed ~* ''a pattern that means this not a hub'' THEN
WHILE transformed ~ '' '' LOOP
spacepos := POSITION( '' '' IN transformed );
transformed := SUBSTRING( transformed FROM 1 TO spacepos - 1 )
|| SUBSTRING( transformed FROM spacepos + 1 );
END LOOP;
END IF;

RETURN transformed;
END;
' LANGUAGE 'plpgsql';

Of course "a pattern that means this is not a hub" could becomplicated.
Another way might be to write the space stripping function just
completely strip spaces, and run both values through it (so hub names
get their spaces stripped as well).

Another possibility would be to use PL/Perl - you could do a lot more
sophisticated string processing, but the function would likely have much
greater overhead...

Hope this ramble is some use.

Regards,
Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267
Are you enrolled at http://schoolreunions.co.nz/ yet?