Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Posts
    60

    Question Unanswered: Need Trigger to update table

    I need to create a trigger that will modify an address field. This is what the trigger needs to do.
    1. Abbreviate address suffiexes i.e. STREET to ST, Street to St, BOULEVARD to BLVD, Boulevard to Blvd. and so on...
    2. Must be able to distinguish between HIGHWAY AND WAY or Highway and way and BROADWAY AND ROAD or Broadway and Road as a couple of examples. I don't want to end up with values like HIGHWay or BRoadWAY.

    So far here's what I have.

    I've declared a cursor and set it equal to this select statement -
    SELECT d.Address1 COLLATE SQL_LATIN1_GENERAL_CP1_CS_AS, i.IDNumber
    FROM dirtydb d INNER JOIN
    inserted i ON d.idnumber = i.idnumber

    WHILE @@FETCH_STATUS = 0
    BEGIN
    SELECT @Index = patindex('%WAY%', @address1)
    IF @Index > 0
    BEGIN
    --PRINT 'Index value: ' + STR(@Index)
    IF @address1 NOT LIKE '%HIGHWAY%' COLLATE SQL_LATIN1_GENERAL_CP1_CS_AS AND @address1 LIKE '%WAY%' COLLATE SQL_LATIN1_GENERAL_CP1_CS_AS
    UPDATE DirtyDB
    SET Address1 = replace(@address1, 'WAY', 'WAY')
    WHERE IDNumber = @IDNumber
    ELSE
    UPDATE DirtyDB
    SET Address1 = replace(@address1, 'WAY', 'Way')
    WHERE IDNumber = @IDNumber
    --PRINT 'Replaced ' + @Address1 + ' with ' + replace(@address1, 'WAY', 'WAY')
    END

    My problem is I am still ending up with Values like HIGHWay. I know its the LIKE key word but I havn't had any luck with fixing it.

    Any insights are welcome and thanks in advance.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Is your instance set up as case sensetive?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    May 2003
    Posts
    60
    No, that's why I'm changing the collation to case sensitive accent sensitive in my comparisons.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •