Saturday, February 25, 2012

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.Is your instance set up as case sensetive?|||No, that's why I'm changing the collation to case sensitive accent sensitive in my comparisons.

No comments:

Post a Comment