Solved

Updating ms sql with special characters

Posted on 2016-11-27
8
44 Views
Last Modified: 2016-11-28
We need to update a column with characters that also include the apostrophe, for example

UPDATE tablename SET String1='/tƒÉÄk…Bz–ñõ'âÜ(sÀ\ÿ$K'ylôû' WHERE column1='blah'

Open in new window


We need to save the value '/tƒÉÄk…Bz–ñõ'âÜ(sÀ\ÿ$K'ylôû'  to the table.

How can we do this?
0
Comment
Question by:rayluvs
  • 4
  • 3
8 Comments
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41903657
So the above query is not working ?

Replace ' to ''
0
 

Author Comment

by:rayluvs
ID: 41903661
It's only the apostrophe that is the problem?  (what about  those other characters: ñõ'âÜÀÿ'ôû?
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41903663
Try...

1. Single ' to '' which are present in between of the string.

'/tƒÉÄk…Bz–ñõ'âÜ(sÀ\ÿ$K'ylôû' --------->  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.

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

Open in new window



O/P


column1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/tƒÉÄk…Bz–ñõ'âÜ(sÀ\ÿ$K'ylôû
Pawan

(2 row(s) affected)

Open in new window

0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41903666
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
0
 

Author Comment

by:rayluvs
ID: 41903682
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?)
0
 
LVL 28

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41903690
try this .. It is working for me..


SET QUOTED_IDENTIFIER OFF;

DECLARE @Str AS VARCHAR(1000) = "'/tƒÉÄk…Bz–ñõ'âÜ(sÀ\ÿ$K'ylôû'"

UPDATE a
SET a.column1 =  @Str
FROM tests a
WHERE column1 = 'blah'

select * from tests

Open in new window



Output


column1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'/tƒÉÄk…Bz–ñõ'âÜ(sÀ\ÿ$K'ylôû'
Pawan

Open in new window

0
 

Author Comment

by:rayluvs
ID: 41903758
Yes!!! U D MAN!

Thanx!
0
 
LVL 3

Expert Comment

by:Ed Dredd
ID: 41903776
blue arrow uses two single quotes while the red arrows are one double quote

-11-28-2016-5-27-57-PM.png
For variables in the SQL, everytime you find a single quote replace it with two single quotes

REPLACE('@variables' , " ' " , " ' ' ");
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Access 2016 - query 23 60
SSRS 2012 r2 - Parm Drop Down has Date/Time 12 33
Run SQL Server Proc from Access 11 31
ServiceCenter IR Query Expressions 1 20
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question