Replace Character on insert trigger

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Replace Character on insert trigger

Author

Topic

srussell
Starting Member

9 Posts

Posted - 2008-11-05 : 11:30:25

I am trying to update an inserted row if a spanish character exists. We need to check the inserted row for the character before we try to replace the character. So if the character does not exist we do not try to replace it. Below is a copy of the code. The code below does it on one row but I need to check each row for multiple characters. I need this query to perform well as it will be on a pretty big database. It works but is there a better way to do it.


ALTER TRIGGER [dbo].[TESTSETS] ON [dbo].[TESTTABLE] FOR INSERT AS

DECLARE @var nvarchar(255)
SELECT @var= (SELECT num FROM inserted)

IF (PATINDEX('%á%',(@var)) > 0)
BEGIN
	UPDATE
	testtable
	SET testtable.num= REPLACE(testtable.num, 'á', 'a')
	FROM testtable
		JOIN inserted on testtable.othernum = inserted.othernum
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-05 : 11:32:45

[code]ALTER TRIGGER [dbo].[TESTSETS] ON [dbo].[TESTTABLE] FOR INSERT AS
BEGIN
UPDATE
testtable
SET testtable.num= REPLACE(testtable.num, 'á', 'a')
FROM testtable
JOIN inserted on testtable.othernum = inserted.othernum
WHERE PATINDEX('%á%',testtable.num) > 0
END[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-05 : 11:34:10

[code]ALTER TRIGGER dbo.TESTSETS ON dbo.TESTTABLE
FOR INSERT
AS

UPDATE tt
SET tt.num = REPLACE(tt.num, 'á', 'a')
FROM testtable AS tt
INNER JOIN inserted AS i on i.othernum = tt.othernum
WHERE i.num LIKE '%á%'[/code]

E 12°55'05.63"
N 56°04'39.26"

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-11-05 : 11:35:05

UPDATE a
SET a.num= REPLACE(a.num, 'á', 'a')
FROM testtable a
Inner JOIN
inserted b on a.othernum = b.othernum
where b.Num like '%á%'

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-11-05 : 11:35:59

2 in a row I'm late on the post :)

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

srussell
Starting Member

9 Posts

Posted - 2008-11-05 : 16:40:47

I have tried all of the above options and the performance to say the least is lacking.

It takes about 1 second per record to insert and update. As opposed to 200 a second with no trigger. Is there any faster way to do this. Below is the exact code I am using. Any help would be much appreciated.


CREATE TRIGGER [dbo].[RemoveSpanishOnInsert] ON [dbo].[AddressesTable] FOR INSERT AS

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

UPDATE		m
SET			m.memPrefix = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(m.memPrefix, 'á', 'a'), 'é', 'e'),'í','i'),'ó','o'),'ú','u'),'Á','A'),'É','E'),'Í','I'),'Ó','O'),'Ú','U'),'ü','u'),'Ü','U'),'ñ','n'),'Ñ','N')
FROM		AddressesTable AS m
INNER JOIN	inserted AS i on i.memMMnum = m.memMMnum and i.memNum = m.memNum
WHERE		(i.memPrefix LIKE '%á%' or i.memPrefix LIKE '%é%' or i.memPrefix LIKE '%í%' or i.memPrefix LIKE '%ó%' or i.memPrefix LIKE '%ú%' or i.memPrefix LIKE '%Á%' or i.memPrefix LIKE '%É%' or i.memPrefix LIKE '%Í%' or i.memPrefix LIKE '%Ó%' or i.memPrefix LIKE '%Ú%' or i.memPrefix LIKE '%ü%' or i.memPrefix LIKE '%Ü%' or i.memPrefix LIKE '%ñ%' or i.memPrefix LIKE '%Ñ%')

UPDATE		m
SET			m.memName = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(m.memName, 'á', 'a'), 'é', 'e'),'í','i'),'ó','o'),'ú','u'),'Á','A'),'É','E'),'Í','I'),'Ó','O'),'Ú','U'),'ü','u'),'Ü','U'),'ñ','n'),'Ñ','N')
FROM		AddressesTable AS m
INNER JOIN	inserted AS i on i.memMMnum = m.memMMnum and i.memNum = m.memNum
WHERE		(i.memName LIKE '%á%' or i.memName LIKE '%é%' or i.memName LIKE '%í%' or i.memName LIKE '%ó%' or i.memName LIKE '%ú%' or i.memName LIKE '%Á%' or i.memName LIKE '%É%' or i.memName LIKE '%Í%' or i.memName LIKE '%Ó%' or i.memName LIKE '%Ú%' or i.memName LIKE '%ü%' or i.memName LIKE '%Ü%' or i.memName LIKE '%ñ%' or i.memName LIKE '%Ñ%')

UPDATE		m
SET			m.memLName = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(m.memLName, 'á', 'a'), 'é', 'e'),'í','i'),'ó','o'),'ú','u'),'Á','A'),'É','E'),'Í','I'),'Ó','O'),'Ú','U'),'ü','u'),'Ü','U'),'ñ','n'),'Ñ','N')
FROM		AddressesTable AS m
INNER JOIN	inserted AS i on i.memMMnum = m.memMMnum and i.memNum = m.memNum
WHERE		(i.memLName LIKE '%á%' or i.memLName LIKE '%é%' or i.memLName LIKE '%í%' or i.memLName LIKE '%ó%' or i.memLName LIKE '%ú%' or i.memLName LIKE '%Á%' or i.memLName LIKE '%É%' or i.memLName LIKE '%Í%' or i.memLName LIKE '%Ó%' or i.memLName LIKE '%Ú%' or i.memLName LIKE '%ü%' or i.memLName LIKE '%Ü%' or i.memLName LIKE '%ñ%' or i.memLName LIKE '%Ñ%')

UPDATE		m
SET			m.memSuffix = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(m.memSuffix, 'á', 'a'), 'é', 'e'),'í','i'),'ó','o'),'ú','u'),'Á','A'),'É','E'),'Í','I'),'Ó','O'),'Ú','U'),'ü','u'),'Ü','U'),'ñ','n'),'Ñ','N')
FROM		AddressesTable AS m
INNER JOIN	inserted AS i on i.memMMnum = m.memMMnum and i.memNum = m.memNum
WHERE		(i.memSuffix LIKE '%á%' or i.memSuffix LIKE '%é%' or i.memSuffix LIKE '%í%' or i.memSuffix LIKE '%ó%' or i.memSuffix LIKE '%ú%' or i.memSuffix LIKE '%Á%' or i.memSuffix LIKE '%É%' or i.memSuffix LIKE '%Í%' or i.memSuffix LIKE '%Ó%' or i.memSuffix LIKE '%Ú%' or i.memSuffix LIKE '%ü%' or i.memSuffix LIKE '%Ü%' or i.memSuffix LIKE '%ñ%' or i.memSuffix LIKE '%Ñ%')

UPDATE		m
SET			m.memAdd1 = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(m.memAdd1, 'á', 'a'), 'é', 'e'),'í','i'),'ó','o'),'ú','u'),'Á','A'),'É','E'),'Í','I'),'Ó','O'),'Ú','U'),'ü','u'),'Ü','U'),'ñ','n'),'Ñ','N')
FROM		AddressesTable AS m
INNER JOIN	inserted AS i on i.memMMnum = m.memMMnum and i.memNum = m.memNum
WHERE		(i.memAdd1 LIKE '%á%' or i.memAdd1 LIKE '%é%' or i.memAdd1 LIKE '%í%' or i.memAdd1 LIKE '%ó%' or i.memAdd1 LIKE '%ú%' or i.memAdd1 LIKE '%Á%' or i.memAdd1 LIKE '%É%' or i.memAdd1 LIKE '%Í%' or i.memAdd1 LIKE '%Ó%' or i.memAdd1 LIKE '%Ú%' or i.memAdd1 LIKE '%ü%' or i.memAdd1 LIKE '%Ü%' or i.memAdd1 LIKE '%ñ%' or i.memAdd1 LIKE '%Ñ%')


UPDATE		m
SET			m.memAdd2 = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(m.memAdd2, 'á', 'a'), 'é', 'e'),'í','i'),'ó','o'),'ú','u'),'Á','A'),'É','E'),'Í','I'),'Ó','O'),'Ú','U'),'ü','u'),'Ü','U'),'ñ','n'),'Ñ','N')
FROM		AddressesTable AS m
INNER JOIN	inserted AS i on i.memMMnum = m.memMMnum and i.memNum = m.memNum
WHERE		(i.memAdd2 LIKE '%á%' or i.memAdd2 LIKE '%é%' or i.memAdd2 LIKE '%í%' or i.memAdd2 LIKE '%ó%' or i.memAdd2 LIKE '%ú%' or i.memAdd2 LIKE '%Á%' or i.memAdd2 LIKE '%É%' or i.memAdd2 LIKE '%Í%' or i.memAdd2 LIKE '%Ó%' or i.memAdd2 LIKE '%Ú%' or i.memAdd2 LIKE '%ü%' or i.memAdd2 LIKE '%Ü%' or i.memAdd2 LIKE '%ñ%' or i.memAdd2 LIKE '%Ñ%')


UPDATE		m
SET			m.memCity = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(m.memCity, 'á', 'a'), 'é', 'e'),'í','i'),'ó','o'),'ú','u'),'Á','A'),'É','E'),'Í','I'),'Ó','O'),'Ú','U'),'ü','u'),'Ü','U'),'ñ','n'),'Ñ','N')
FROM		AddressesTable AS m
INNER JOIN	inserted AS i on i.memMMnum = m.memMMnum and i.memNum = m.memNum
WHERE		(i.memCity LIKE '%á%' or i.memCity LIKE '%é%' or i.memCity LIKE '%í%' or i.memCity LIKE '%ó%' or i.memCity LIKE '%ú%' or i.memCity LIKE '%Á%' or i.memCity LIKE '%É%' or i.memCity LIKE '%Í%' or i.memCity LIKE '%Ó%' or i.memCity LIKE '%Ú%' or i.memCity LIKE '%ü%' or i.memCity LIKE '%Ü%' or i.memCity LIKE '%ñ%' or i.memCity LIKE '%Ñ%')

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-11-05 : 16:58:20

There's a few issues. First try not to insert each value 1 at a time. Insert them all at once, create a tmp table if needed i.e.



Insert into Addresstable(Col1,Col2,Col3)
Select Col1,Col2,Col3
from
#Tmptable a

This way it will only be running the trigger one time for every record rather than each time the record is inserted.

Regardless of the first step , use this update instead. It should well out perform.


UPDATE		m
SET			
m.memPrefix = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(m.memPrefix, 'á', 'a'), 'é', 'e'),'í','i'),'ó','o'),'ú','u'),'Á','A'),'É','E'),'Í','I'),'Ó','O'),'Ú','U'),'ü','u'),'Ü','U'),'ñ','n'),'Ñ','N')
,m.memName = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(m.memName, 'á', 'a'), 'é', 'e'),'í','i'),'ó','o'),'ú','u'),'Á','A'),'É','E'),'Í','I'),'Ó','O'),'Ú','U'),'ü','u'),'Ü','U'),'ñ','n'),'Ñ','N')
,m.memLName = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(m.memLName, 'á', 'a'), 'é', 'e'),'í','i'),'ó','o'),'ú','u'),'Á','A'),'É','E'),'Í','I'),'Ó','O'),'Ú','U'),'ü','u'),'Ü','U'),'ñ','n'),'Ñ','N')
,m.memSuffix = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(m.memSuffix, 'á', 'a'), 'é', 'e'),'í','i'),'ó','o'),'ú','u'),'Á','A'),'É','E'),'Í','I'),'Ó','O'),'Ú','U'),'ü','u'),'Ü','U'),'ñ','n'),'Ñ','N')
,m.memSuffix = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(m.memSuffix, 'á', 'a'), 'é', 'e'),'í','i'),'ó','o'),'ú','u'),'Á','A'),'É','E'),'Í','I'),'Ó','O'),'Ú','U'),'ü','u'),'Ü','U'),'ñ','n'),'Ñ','N')
,m.memAdd1 = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(m.memAdd1, 'á', 'a'), 'é', 'e'),'í','i'),'ó','o'),'ú','u'),'Á','A'),'É','E'),'Í','I'),'Ó','O'),'Ú','U'),'ü','u'),'Ü','U'),'ñ','n'),'Ñ','N')
,m.memAdd2 = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(m.memAdd2, 'á', 'a'), 'é', 'e'),'í','i'),'ó','o'),'ú','u'),'Á','A'),'É','E'),'Í','I'),'Ó','O'),'Ú','U'),'ü','u'),'Ü','U'),'ñ','n'),'Ñ','N')
,m.memCity = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(m.memCity, 'á', 'a'), 'é', 'e'),'í','i'),'ó','o'),'ú','u'),'Á','A'),'É','E'),'Í','I'),'Ó','O'),'Ú','U'),'ü','u'),'Ü','U'),'ñ','n'),'Ñ','N')
FROM		AddressesTable AS m
INNER JOIN	inserted AS i on i.memMMnum = m.memMMnum and i.memNum = m.memNum

or option 2 is to just change the column to a nvarchar column and not worry about it.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-11-05 : 17:01:22

Maybe there is another way to do this, but without a complete rollup i would say try to get your updates together i.e. one for all:


UPDATE m
SET m.memPrefix = replace(replace(...and all that shit),
    m.memName   = replace(replace(...and all that shit),

   ... and so on ...

FROM AdressesTable AS m
INNER JOIN ...
--WHERE 1=1

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-11-05 : 17:02:47

Arrgh - too late!

No, you're never too old to Yak'n'Roll if you're too young to die.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-05 : 17:06:09

[code]create function fn_byebyeEspanol(@str varchar(max))
returns varchar(max)
as
begin return REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@str, 'á', 'a'), 'é', 'e'),'í','i'),'ó','o'),'ú','u'),'Á','A'),'É','E'),'Í','I'),'Ó','O'),'Ú','U'),'ü','u'),'Ü','U'),'ñ','n'),'Ñ','N')
end

insert into AddressesTable (memprefix...) values (dbo.fn_byebyeespanol(memPrefix),...)
[/code]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-11-05 : 17:12:25