Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Updating ms sql with special characters

Posted on 2016-11-27
8
Medium Priority
?
81 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 30

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 30

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
PowerShell Core for Advanced Linux Administrators

Understand advanced principals around Powershell Core with a focus on the Linux Administrator.  This course covers how to administer numerous environments across multiple platforms including Linux, Azure, AWS, and Google Cloud from a single shell instance.

 
LVL 30

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 30

Accepted Solution

by:
Pawan Kumar earned 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

670 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