Обсуждение: search and replace possible on SQL?

Hi all

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;

On Mon, May 06, 2002 at 04:02:23PM +0200,
  "Duncan Adams  (DNS)" <duncan.adams@vcontractor.co.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.

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?