Replace Help 2

[mossbs]

Hi guys,

Trying to update a column in my db by prefixing the values in there with values from another column in another table....

code i have so far is...

update p
set p.Password = replace(p.Password,p.Password,d.Code+p.Password)
from Password p
join DealerEmployee de on de.Id_User = p.Id_User
join Dealer d on d.Id_Dealer = de.Id_Dealer
where p.Password = 'xxxxx'

however this is producing the following error...

Cannot resolve collation conflict for replace operation.

any ideas guys how to fix this error?
or am i going completely the wrong way about this in the first place?

cheers,

Dan.

 

[gmmastros]

It looks like you don't really need to use the replace function. Instead, you could just do this...

Set p.Password = d.code + p.password

But... you'll still have the collation problem. Collation problems are not too difficult to deal with, once you understand what a collation is.

Collations determine how string data is sorted and compared to other string data. For example, should E and e be sorted together? Should E and e compare the same (case insensitive compare)? I suspect your password column has a case sensitive collation, and all of your other columns are case insensitive.

Can you run this and post the results?

Select table_Name, column_name, Collation_Name 
From   information_Schema.columns
Where (Table_Name = 'Password' and Column_Name = 'Password')
      Or
      (table_name = 'Dealer' and column_name = 'code')

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mossbs]

hi george,

i managed to do it the ugly way in the end - just set up a temp table in a test database and then combined the columns with '+' like you suggested above in there to get around the collation problem.

cheers for the reply though!

 

[gmmastros]

If you would have posted the output like I asked, I would have shown you how to modify your original query to make it work. When you have a database where there is a collation mismatch, it's very important to understand what the collations are doing, and how to effectively use them.

I wrote a SQL Server utility that can analyse your database and highlight collation mis-matches. You can download this free utility here:

http://sqlcop.lessthandot.com/

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mossbs]

would be interested to see how that works... cheers George....

table_Name	column_name	Collation_Name
Dealer	Code	Latin1_General_CI_AI
Password	Password	Latin1_General_BIN

 

[gmmastros]

Obviously, the problem is here:

replace(p.Password,p.Password,d.Code+p.Password)

If we look at the collations involved:

replace(Latin1_General_BIN, Latin1_General_BIN, [!]Latin1_General_CI_AI + Latin1_General_BIN[/!])

So, we're trying to concatenate two different collations. If we use the COLLATE clause, the problem goes away. Like this:

update p
set p.Password = replace(p.Password,p.Password,d.Code [!] COLLATE Latin1_General_BIN[/!] +p.Password)
from Password p
join DealerEmployee de on de.Id_User = p.Id_User
join Dealer d on d.Id_Dealer = de.Id_Dealer
where p.Password = 'xxxxx'

Here's a (some what) similar example, with some dummy tables and data.

Declare @Password Table(UserId Int, Password VarChar(50) Collate Latin1_General_BIN)
Declare @User Table(UserId Int, Name VarChar(20) Collate Latin1_General_CI_AI)

Insert Into @User Values(1, 'George')
Insert Into @User Values(2, 'Dan')

Insert Into @Password Values(1, 'Foo')
Insert Into @Password Values(2, 'Bar')

Update P
Set    P.Password = Replace(P.Password, P.Password, U.Name + P.Password)
From   @Password P
       Inner Join @User U
         On P.UserId = U.UserId
         
Select * From @Password

When you run the code above, you will get this error:
[tt][red]Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.[/red][/tt]

But, if you modify the code slightly (to this), it works.

Declare @Password Table(UserId Int, Password VarChar(50) Collate Latin1_General_BIN)
Declare @User Table(UserId Int, Name VarChar(20) Collate Latin1_General_CI_AI)

Insert Into @User Values(1, 'George')
Insert Into @User Values(2, 'Dan')

Insert Into @Password Values(1, 'Foo')
Insert Into @Password Values(2, 'Bar')

Update P
Set    P.Password = Replace(P.Password, P.Password, U.Name [!]Collate Latin1_General_BIN[/!] + P.Password)
From   @Password P
       Inner Join @User U
         On P.UserId = U.UserId
         
Select * From @Password

Most programmers know that SQL Server can do data type conversions for you, but that it's best not to rely upon this. It's better to convert things for yourself.

For example, what is the output of the following code:
[tt]Select '10' + 20[/tt]

There are really only 2 (reasonable) choices. It's either 30 or '1020'. In this example, if you want to add as numbers, it's better to cast/convert the string to a number. If you want string concatenation, it's better to cast/convert the number to a string.

Similarly with strings that have different collations, it's better to specify the collation. This isn't always necessary, but whenever you concatenate and/or compare strings with different collations, it's better to specify the collation instead of relying upon SQL Server to do it for you.

Make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mossbs]

Ah! That makes perfect sense.

Great post and great explanation.

Cheers George!

 

[sparkbyte]

Great post George! A Statr and I'm going to add to my archive for later use.

Thanks

John Fuhrman