jana
asked on
Updating ms sql with special characters
We need to update a column with characters that also include the apostrophe, for example
We need to save the value '/tƒÉÄk…Bz–ñõ'âÜ(sÀ\ÿ$K'yl ôû' to the table.
How can we do this?
UPDATE tablename SET String1='/tƒÉÄk…Bz–ñõ'âÜ(sÀ\ÿ$K'ylôû' WHERE column1='blah'
We need to save the value '/tƒÉÄk…Bz–ñõ'âÜ(sÀ\ÿ$K'yl
How can we do this?
ASKER
It's only the apostrophe that is the problem? (what about those other characters: ñõ'âÜÀÿ'ôû?
Try...
1. Single ' to '' which are present in between of the string.
'/tƒÉÄk…Bz–ñõ'âÜ(sÀ\ÿ$K'y lôû' ---------> N'/tƒÉÄk…Bz–ñõ''âÜ(sÀ\ÿ$K''ylôû'
2. Use N before the string you want to update
N'/tƒÉÄk…Bz–ñõ''âÜ(sÀ\ÿ$K ''ylôû'
3. The column should be of NVarchar type.
O/P
1. Single ' to '' which are present in between of the string.
'/tƒÉÄk…Bz–ñõ'âÜ(sÀ\ÿ$K'y
2. Use N before the string you want to update
N'/tƒÉÄk…Bz–ñõ''âÜ(sÀ\ÿ$K
3. The column should be of NVarchar type.
CREATE TABLE tests
(
column1 NVARCHAR(MAX)
)
GO
INSERT INTO tests VALUES (N'blah'),(N'Pawan')
UPDATE a
SET a.column1 = N'/tƒÉÄk…Bz–ñõ''âÜ(sÀ\ÿ$K''ylôû'
FROM tests a
WHERE column1 = 'blah'
SELECT * FROM tests
O/P
column1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/tƒÉÄk…Bz–ñõ'âÜ(sÀ\ÿ$K'ylôû
Pawan
(2 row(s) affected)
Update..
2. N before the string is not required is the data type is of type Varchar. It is required if it is of type NVarchar.
3. The column can be of NVarchar or Varchar.
Hi rayluvs,
Is this done :) ?
Regards.,
Pawan
2. N before the string is not required is the data type is of type Varchar. It is required if it is of type NVarchar.
3. The column can be of NVarchar or Varchar.
Hi rayluvs,
Is this done :) ?
Regards.,
Pawan
ASKER
So there is no other way to update without replacing all ' within the value?
Finally, the special characters ñõ'âÜÀÿ'ôû, there is no problem saving them to the table? Any consideration?
(we are using a encryption/decryption in vb prior saving to the table routine and that is why those strange value... we want to make sure that those random characters are ok to save or is there any other additional considerations?)
Finally, the special characters ñõ'âÜÀÿ'ôû, there is no problem saving them to the table? Any consideration?
(we are using a encryption/decryption in vb prior saving to the table routine and that is why those strange value... we want to make sure that those random characters are ok to save or is there any other additional considerations?)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes!!! U D MAN!
Thanx!
Thanx!
Replace ' to ''