Replace character query for SQL 2012

Hi everyone
I need to change characters in all tables/colums of a DB.  Here's to code that I'm using to change one table/column at a time:
BEGIN TRANSACTION; 
UPDATE dbo.clients_d
  SET Suivi=REPLACE(Suivi,'é','é'); 
COMMIT TRANSACTION;

Open in new window


What could I use to change it across all tables/colums?

Thanks!
Daniel VachonIT ProfessionalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
Declare @Suivi nvarchar(30) ;
--try with N:
SET @Suivi=REPLACE(Suivi,N'é',N'é');

--make sure to use N not n
0
Daniel VachonIT ProfessionalAuthor Commented:
But from what I understand I I use update dbo.clients, it will change only within that table, and "suivi" is my column in that table so what will N do in this command?
0
Mike EghtebasDatabase and Application DeveloperCommented:
For international characters, you need N
UPDATE dbo.clients_d
  SET Suivi=REPLACE(Suivi,N'é',N'é');

if you check clients_d table for Suivi column, it must have data type of nvarchar(somenumber). If it has  varchar(somenumber), then the table has to be altered to accept values like 'é' or 'é'
1
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Vitor MontalvãoMSSQL Senior EngineerCommented:
What could I use to change it across all tables/colums?

Open in new window

By dynamic query but this can be very complex to do since you need to work with cursors and retrieve all tables and columns for the database then check which ones can store strings and be careful if some of those are PK or FK since you can break some relationship.

I would go one by one and script them together so you can save the script for later use.

By the way, how come those characters appeared?
1
Éric MoreauSenior .Net ConsultantCommented:
You need to go dynamic. Here is a script I found elsewhere that you can add to a cursor looping through the tables of database:
Declare @updateSQL varchar(1000)
Set @updateSQL = 'Update Table1 Set '
Select @updateSQL = @updateSQL + '['+column_name+'] = p.[' +column_name+'],' From information_schema.columns
where [table_name]='Temp' order by ordinal_position
Set @updateSQL = LEFT(@updateSQL,len(@updateSQL)-1)
Set @updateSQL = ' From Temp P,Table1 Where table1.id=P.id'
sp_ExecuteSQL @updateSQL

Open in new window

1
Vitor MontalvãoMSSQL Senior EngineerCommented:
Éric, did you test it?
Doesn't seems the solution for this particular case.
0
Éric MoreauSenior .Net ConsultantCommented:
Starting with the dynamic query and adding it to a cursor would solve the issue.
0
Daniel VachonIT ProfessionalAuthor Commented:
@Mike so if I add N to the command, will future entries be corrected?  As I'm also looking for a way to solve this issue
( see post http://www.experts-exchange.com/questions/28714648/SQL-collation-with-php.html#a40981916 )
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I mean, you're posting a query with a join and LEFT function. This can mislead the author to the correct solution.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
The final code would look like this:
declare @cmd varchar(max)
declare @tbl varchar(256)
declare csr cursor for select table_name from information_schema.tables

open csr
fetch next from csr into @tbl
while (@@fetch_status = 0)
begin
  set @cmd = 'update "' + @tbl + '" set '
  select @cmd = @cmd + '"'+column_name+'" = replace("'+column_name+'", ''é'',''é''), ' from information_schema.columns where data_type = 'varchar' and table_name = @tbl
  if @@rowcount > 0
  begin
    set @cmd = left(@cmd, len(@cmd)-1)
    print @cmd
    -- exec (@cmd)
  end
  fetch next from csr into @tbl
end

close csr
deallocate csr

Open in new window

This will not perform any replace, but show what it would do. Remove the -- to allow for applying the command.

I'm not clear about the requirement to use N for this updates. We cannot tell if using N in your application helps to stay away from this issue arising, because that depends on how the applications delivers the command and its parameters on insert or update.
0
Daniel VachonIT ProfessionalAuthor Commented:
Wow, now I'm lost :-\

@Vitor The program is PHP based and was purchased by a school board and no support is available, it's used for quotes, client information but we reside in Quebec, Canada so it's french.  Many company names, street address, contact name contains accentuated characters.

So I'm changing them manually ( for now) but looking into a permanent solution but I have no idea what I'm doing and have no training at all in Database management or php language and I don't have any other ressources than internet...
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
If your single command as shown initially works (and you said so), my code of http:#a40991174, executed in SQL Server Management Studio, should work fine.

With PHP you will most probably have UTF-8 as character encoding. That should encode anything beyond the standard ASCII set by at least two characters, and the ASCII chars as a single one. é is not part of the original ASCII, and that is why you get those two strange characters instead.
As MSSQL does not know of UTF-8, you'll either need to recode manually in PHP, translating to the local codepage which certainly contains é and the other french accented characters, or translate to UTF-16 (fixed two bytes per character, Windows jargon is "Unicode"). But if that works again depends on the way the command is generated - everything as a single string, or with parameters, ...

If that sounds confusing and complicated - yes, it is.
1
Daniel VachonIT ProfessionalAuthor Commented:
So if I understand, the issue is most likely coming from the php and not the collation in mssql and converting all php files to utf16 could solve my problems?!
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
The PHP scripts are the first point to check. MsSql collation is not the culprit.
0
Daniel VachonIT ProfessionalAuthor Commented:
Ok, i'll get to converting the files!
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I don't know PHP so my first thought was to change the database collation but after reading Qlemo comments I think you should give a first try to PHP.
0
ZberteocCommented:
Hi Daniel,

What you need is a function to replace any "bad" characters you may have because I believe é is not the only one. I had to deal with similar issue an I built this function:
USE YourDatabase
GO
CREATE FUNCTION [dbo].[fnReplaceBadFrechCharacters] ( 
      @bad_french_characters varchar(max)
) 
RETURNS varchar(max)
AS
BEGIN
	
	RETURN 
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
		replace(
			@bad_french_characters
		,'é','é') 
		,'è','è') 
		,'ô','ô') 
		,'ç','ç') 
		,'ü','ü') 
		,'É','É') 
		,'û','û')
		,'È','È')
		,'ÃŽ','Î')
		,'à','à')
		,'î','î')
		,'ï','ï')
		,'ë','ë')
		,'â','â')
		,'ê','ê')
		,'Ô','Ô')
		,'Â','Â')
		,'ä','ä')
		,'ö','ö')
		,'ó','ó')

		,'À','À')
		,'Ã','Á')
		--,'Â','Â')
		,'Ã','Ã')
		,'Ä','Ä')
		,'Ç','Ç')
		--,'È','È')
		--,'É','É')
		,'Ê','Ê')
		,'Ë','Ë')
		,'ÃŒ','Ì')
		,'Ã','Í')
		,'ÃŽ','Î')
		,'Ã','Ï')
		,'Ã’','Ò')
		,'Ó','Ó')
		--,'Ô','Ô')
		,'Õ','Õ')
		,'Ö','Ö')
		,'Ù','Ù')
		,'Ú','Ú')
		,'Û','Û')
		,'Ãœ','Ü')
		,'Ã','Ý')
		--,'à','à')
		,'á','á')
		--,'â','â')
		,'ã','ã')
		--,,'ä','ä')
		--,'ç','ç')
		--,'è','è')
		--,'é','é')
		--,'ê','ê')
		--,'ë','ë')
		,'ì','ì')
		,'í','í')
		--,'î','î')
		--,'ï','ï')
		,'ò','ò')
		--,'ó','ó')
		--,'ô','ô')
		,'õ','õ')
		--,'ö','ö')
		,'ù','ù')
		,'ú','ú')
		--,'û','û')
		--,'ü','ü')

		,'ý','ý')
		,'þ','þ')
		,'ÿ','ÿ')
		,'Ã…','Å')
		,'Æ','Æ')
		,'Ã','Ð')
		,'Ñ','Ñ')
		,'×','×')
		,'Ø','Ø')
		,'Þ','Þ')
		,'ß','ß')
		,'Ã¥','å')
		,'æ','æ')
		,'ð','ð')
		,'ñ','ñ')
		,'÷','÷')
		,'ø','ø')
END 

Open in new window

You can create the function in any database you want, doesn't even have to be the same with the one you will update, but make sure you update the YourDatabase with the actual database name where you will create the function.

Use this in conjunction with a code that will scan all your columns from all tables that are of type like %CHAR and built the update statements for you which then you will execute in a new window. Here is the code:
SELECT id,
    CASE 
	   WHEN ID=1 THEN '

GO
update ['+table_name+'] set 
     ['+column_name+']'
	   ELSE 
'   ,['+column_name+']'
    end+'=[YourDatabase].[dbo].[fnReplaceBadFrechCharacters](['+column_name+'])'
FROM
(

    SELECT 
	   row_number() OVER (partition by c.table_name ORDER BY c.table_name,c.column_name) ID, 
	   c.table_name, 
	   c.column_name
    FROM 
	   information_schema.columns c 
	   INNER JOIN information_schema.tables t
		  ON t.table_name=c.table_name
    WHERE 
	   c.data_type LIKE '%char'
	   AND t.table_type='BASE TABLE'
) q
ORDER BY 
  table_name,
  ID

Open in new window

Again, make sure you replace YourDatabase with the database name you used for the function to create it! Copy the result from this query and paste it in a different window in SSMS and just execute it.

I recommend you to keep the indentations , empty lines, new lines as they are in my scripts, it helps to generate formatted statements.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dbbishopCommented:
One thing to keep in mind is if there are any foreign keys to columns you are going to change. For example, if you have a state table with a state code and name, and you want to change state code from MÒ to MO but have MÒ stored as the state code as part of an address in another table, you will have issues. Even if you disable FK constraints, if there are problems during the conversion you might still have problems re-enabling them. MAKE SURE YOU HAVE A GOOD BACKUP!

I like Zberteoc code for two reasons. You generate all the code as a single block, instead of executing it in chunks within a cursor, and he has made steps to insure you only update character data (although you MIGHT want to consider if you have TEXT or NTEXT data.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
dbbishop, my code in http:#a40991174 only updates varchar columns, too (extending to char is easy).
The "block" stuff has absolutely no meaning, and code does not get executed, you have to copy & paste the result to execute. That usually has no advantage over allowing for immediate execution, as soon as you are convinced the generated code is correct.
0
ZberteocCommented:
Good point with FKs!

In regards with the TEXT data type you need to change the WHERE clause like this:

    WHERE
         (c.data_type LIKE '%char' OR c.data_type LIKE '%text')
         AND t.table_type='BASE TABLE'

I would suggest to filter out the columns that are selected in the script because I suspect only city names, states, counties, person names and such are candidates for french characters. This will also ease up the load. If the tables are really big then it is not recommended to run the statements for the whole thing, maybe it can be broken in smaller chunks.
0
ZberteocCommented:
"The "block" stuff has absolutely no meaning, and code does not get executed, you have to copy & paste the result to execute. That usually has no advantage over allowing for immediate execution, as soon as you are convinced the generated code is correct."

I beg to differ.

First, the code IS generated correctly and that can be tested easily and immediately. Beside that your code is prone the the exact same risk, it still generates some code despite the fact is in a loop! Why is your method safer than mine?

Second, I personally prefer the "block" code because I can run it one statement at a time if I want.  I can control it, I can easily adapt it and if something goes wrong, an error, you will be pointed immediately to the exact statement by double clicking in the Messages tab. if there is an error on one tables, the others will still execute if ran in all. A dynamic query executed in loop is more difficult to debug and for that you will still have to generate the code first and test it separately!

It is no big deal to copy and paste and actually is better to have a look at the code before you execute it.
0
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Did I say anything in regard of being more safe? I said "no advantage". I agree testing is required, and I do that by restricting the loop. I definitely do not prefer to copy over 10MB of script, and never will execute millions of lines manually.

Anyway, the comment was directed to dbbishop. I don't say your code is worse or better than mine.
0
ZberteocCommented:
Quelmo, you made some statements, which I found questionable, in regards to the method I suggested and I just answered to that. The fact that you addressed to dbbishop has no relevance, we discuss here about solutions and methods. I thought clarifying was needed.

Any method is good as long as it solves the problem without causing complications down the road..
0
awking00Commented:
You might consider using the translate function, if you're going to dynamically loop through the information_schema views to get the table and column_names. For example, the following will replace all the characters with diacritical marks in the second parameter with all the characters without diacritical marks in the third parameter in that order -
select translate(<information_schema.columns.column_name>,'ªµºÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÑÒÓÔÕÖÙÚÛÜYßàáâãäåæçèéêëìíîï¿ñòóôõöùúûüÿ,'auoAAAAAAACEEEEIIIINOOOOOUUUUYSaaaaaaaceeeeiiiiDnooooouuuu)
from <information_schema.columns.table_name>
0
ZberteocCommented:
TRANSLATE function doesn't exist in MS SQL Server.
0
awking00Commented:
Sorry about that. For some reason, I thought it was added in 2012, but it can be created.
0
ZberteocCommented:
I have already done that but for this exact purpose rather than a general approach. Beside that I don't think the TRANSLATE would work because it "translates" character for character, if I understood correctly, while here we need to replace a series of 2 characters with 1 character.
0
Daniel VachonIT ProfessionalAuthor Commented:
I appreciate all the comments, and sorry for not replying.  I'm testing the software on another server with MySQL, verifying if the problem of wrong characters persist...  Once I have solved the collation issues changing the characters, then I'll start modifying the current DB and decide if it stays on SQL 2012 or moving the data to Mysql.

And also, I'm completely lost in all the code  here ;)
0
ZberteocCommented:
No offense Daniel but what you are doing with MySQL has nothing to do with the question you posted here.

You asked for a solution in MS SQL Server, which was given and time was spent benevolently by people to do that. What you need to do is to test the solution in MS SQL Server and if it works give the reward accordingly. If you want to decide between MS SQL and MySQL then open a different question.
0
Daniel VachonIT ProfessionalAuthor Commented:
No offense taken.
I know it's not related, but one way or another, I'm going to have to correct the data.  Once corrected I will move it to another server if MySQL works best, if not it will remain on the SQL 2012 server.  But the wrong characters still need to be modified and I would prefer have the corrections made before moving the db if that is the path I choose.

So the  original question is still valid and points will be awarded accordingly, I was simply explaining as the WHY I was not replying to comments.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.