TSQL QUERY HELP

sas77
sas77 used Ask the Experts™
on
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!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
Include all required CHAR / Varchar columns in that table to make it work
UPDATE dbo_DimAddress
SET CITY = replace(CITY, 'ã', 'a')

Open in new window

AneeshDatabase Consultant
Top Expert 2009

Commented:
Hello sas77,

You need to find the character first, possibbly that should be an Enter charecter, to make sure this run this
SELECT ASCII(City), CHAR(ASCII(City) )  FROM urTable where city like '%umlaut%'

Now use the following update --run this on a backup

UPDATE dbo_DimAddress
SET City =REPLACE(City, CHAR(the Above integer value ) , '' )
WHERE City like '%'+CHAR(the Above integer value ) +'%'


Regards,

Aneesh

Author

Commented:
I CANNOT FIND ANY UMLAUT CHARACTERS WHEN I EXECUTED THIS

SELECT ASCII(City), CHAR(ASCII(City) )  FROM urTable where city like '%umlaut%'
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

AneeshDatabase Consultant
Top Expert 2009

Commented:
try this way
SELECT ASCII(City), CHAR(ASCII(City) )  
FROM urTable
where UPPER(city) NOT like '[A-Z]%'

Author

Commented:
Will it take care of lower case letters also,

SELECT ASCII(City), CHAR(ASCII(City) )  
FROM dimaddress
where UPPER(city) NOT like '[A-Z]%'

following are some of the results,please let me know what to do with this.


(No column name)      (No column name)
50                                  2
32       
54                                  6
53                                  5
52                                  4
56                                  8
53                                  5
56                                  8
56                                  8
49                                  1
AneeshDatabase Consultant
Top Expert 2009

Commented:
ensure that you run this on Backup data

Update urTable
SET City = RIGHT(City, LEN(City) -1 )
WHERE UPPER(city) NOT like '[A-Z]%'

Update urTable
SET City = LEFT(City, LEN(City) -1 )
WHERE REVERSE(UPPER(City) ) NOT LIKE '[A-Z]%'

Author

Commented:
can you explain before i do that, What this is doing.
AneeshDatabase Consultant
Top Expert 2009

Commented:
it trims out the left most & right most characters in case those are not alphabets

Author

Commented:
but i need to replace that special character with the proper alphabet like

'ã', to  'a'
AneeshDatabase Consultant
Top Expert 2009

Commented:
oopzs, i totally misread your question , did u try  the post by 'rrJegan17'

Author

Commented:
ya i tried that it is working, but is there any efficient way than that.

Like i am encountering mostly vowels aeiou.

Author

Commented:
Thanks
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> but is there any efficient way than that.

This would be the efficient and clean way of doing it.

Since it is with the International Characters, we have to identify each and every characters to be changed along with the desired values manually.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial