July 17, 2009 at 9:23 am
There is special character which is populating mostly in city column which we call as umlaut is there any possible way to write a script to remove by updating that table where it contains in any of the column in that table.
Table name: DimAddress
Columns Effecting this character: city, country, state
Example:Table: 'dbo_DimAddress', Column: 'CITY', Value: 'São Paulo - Sp'.
Help Appreciated!
July 17, 2009 at 9:29 am
This is a simple way.
UPDATE dbo_DimAddress
SET city = replace(city, 'ã', 'a'),
country = replace(country, 'ã', 'a'),
state = replace(state, 'ã', 'a')
WHERE city LIKE '%ã%'
OR country LIKE '%ã%'
OR state LIKE '%ã%'
You could do it for any of the special chars to replace.
Hope this helps
Gianluca
-- Gianluca Sartori
July 17, 2009 at 9:38 am
Be careful with doing updates on a table-wide basis, if the table is very large and/or in active use. Doing an update on the entire table requires you to lock the entire table, and any other updates, inserts, or deletes happening on a row will be forced to wait until the update finishes. If the update takes a long time, you could have a large number of time outs as a result. If the table is large, you would be better off doing the updates in a batch process.
July 17, 2009 at 9:54 am
Can you please let me know how to do in batch process.
July 17, 2009 at 10:58 am
There's several different ways of doing it. The way I usually do it is to make a temp table with all the records that need to be updated, and then go through that table one record at a time, and update the real table with the ID's that I store in the temp table.
Something like this:
CREATE TABLE #TempTable
(
ID INT PRIMARY KEY
)
INSERT INTO #TempTable
SELECT [ID] FROM [Table] WHERE [WhereClauses] ORDER BY ID
DECLARE @CurrID INT
DECLARE @OldID INT
SET @CurrID = 0
SET @OldID = 0
WHILE 1 = 1
BEGIN
SELECT TOP 1 @CurrID = ID
FROM #TempTable
WHERE ID > @OldID
ORDER BY ID
IF @OldID = @CurrID
BREAK
UPDATE [Table]
SET [DoStuff]
WHERE ID = @CurrID
SET @OldID = @CurrID
END
DROP TABLE #TempTable
July 19, 2009 at 9:00 pm
srathna77 (7/17/2009)
Can you please let me know how to do in batch process.
Did any of that work for you?
--Jeff Moden
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply