By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,984 Members | 1,386 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,984 IT Pros & Developers. It's quick & easy.

Replace Single Quote to Double quotes in a String

P: 7
Hello There,

I'm stuck with converting a single quote to double quotes in an incoming string in the SQL Server 2012. The target field is an NVARCHAR field and the incoming string has foreign characters with some single quotes in between. I need to get all the single quote's changed to double quotes in order to load them in the SQL Server as it is. Can someone help me with this issue? I have tried some ways found online, but no luck at all.

Thanks in advance,
Selva.
4 Weeks Ago #1
Share this Question
Share on Google+
14 Replies


Rabbit
Expert Mod 10K+
P: 12,349
What code have you tried so far?
4 Weeks Ago #2

P: 7
Hello Rabbit,

Thank you for getting back,

select replace(N'РВАТ'ЕЛЬ РЫЧА'ЖКОВЫЙ У'РРВ-101.6', Char(39), Char(34)) Foreign_Chars;

select replace(N'РВАТ'ЕЛЬ РЫЧА'ЖКОВЫЙ У'РРВ-101.6','''', '''') Foreign_Chars;

The above are two queries, I tried.
4 Weeks Ago #3

Rabbit
Expert Mod 10K+
P: 12,349
You can't do this on a static string in the SQL, it's already too late. If you need to use a static string, you'll have to do the replace manually.
4 Weeks Ago #4

P: 7
Thank you, Rabbit.

I'm reading the values from an Excel sheet as my source of input and I have some foreign language characters in the strings and in my task I will be using Oracle EDQP as an Interface tool to load them in the Target(SQL Server 2012).

I don't want to do it manually, I wanted to figure out a way to replace them in the SQL query. When I have used a regex option inside the tool(Oracle EDQP)1 to replace all the single quote to double quotes in the incoming string, it delayed my Job to run for hours which use to complete in few mins/secs usually.

Below is the Query, I have used in the processing stage to load my values,
insert into Selva_temp_nvarchar_05_23_2019
(SZEDLN, SZDSC)
values
(?,N'$?');

The above query loaded all the strings without any single quotes as expected but not with single quote in between.

SZDSC NVARCHAR(1000) - It gets all the foreign characters as input from the sheet
4 Weeks Ago #5

Rabbit
Expert Mod 10K+
P: 12,349
If you're using a parameterized query, you shouldn't have to escape the strings right? What happens if you just use this without telling it to interpret it as a literal string?

Expand|Select|Wrap|Line Numbers
  1. insert into Selva_temp_nvarchar_05_23_2019
  2.  (SZEDLN, SZDSC)
  3.  values
  4.  (?,?);
4 Weeks Ago #6

P: 7
If I load with just (?,?) it loads everything but the foreign characters are replaced with ? in the target table.
4 Weeks Ago #7

Rabbit
Expert Mod 10K+
P: 12,349
The reason you're seeing question marks is because the code you're using to call the insert is not defining the string as Unicode. I haven't used Oracle EDQP but there should be a way for you to tell it the string is a Unicode.

For example, in C#, you would do something like this:
Expand|Select|Wrap|Line Numbers
  1. cmd.Parameters.Add(
  2.                 new SqlParameter("@name",
  3.                 SqlDbType.NVarChar, 100) {
  4.                 Value = "ŞİĞİL"
  5.             });
4 Weeks Ago #8

NeoPa
Expert Mod 15k+
P: 31,347
Just as an aside here - replacing single-quote (') characters with double-quote (") characters is probably not your best option. I know it gets recommended everywhere but that doesn't mean it's a good idea. If you want to end up with the correct string after using it as a literal in SQL then you need to double it up.
IE. Two single-quotes rather than a single double-quote.
Expand|Select|Wrap|Line Numbers
  1. REPLACE([YourString],'''','''''')
4 Weeks Ago #9

P: 7
You are right! That is why I use the below insert query from the Oracle EDQP Tool, where you could see the N'$?' for SZDSC column which takes the Unicode string to the target field.

insert into Selva_temp_nvarchar_05_23_2019
(SZEDLN, SZDSC)
values
(?,N'$?');

Now, my problem is I wanted to convert those single quotes to two single quotes before it hits SQL compiler from the EDQP Tool.
3 Weeks Ago #10

P: 7
Thank for getting back, NeoPa.

You are correct, even I came across this statement online somewhere. Now my problem is to cheat the SQL compiler with the same string with single quotes in it, when it comes thru the Tool I'm using.
3 Weeks Ago #11

NeoPa
Expert Mod 15k+
P: 31,347
And, just to be clear, do you feel you can do that now? Or do you feel stuck somewhere still?
3 Weeks Ago #12

P: 7
I'm stuck with it still. Thanks for asking.
3 Weeks Ago #13

NeoPa
Expert Mod 15k+
P: 31,347
That's fine. I don't know your project so can you just explain how it is that you tried to use my suggestion (to use REPLACE()) and what the results were. You may also need to explain why those results should be considered a failure if it isn't obvious.

Clearly, you should also do the same for anything that anyone else has posted in order to help and encourage them to continue to offer their time and energy to assist you. Unless you give clear responses we will always struggle to be helpful.

I'm not able to comment on Rabbit's posts as that's outside of my area of expertise nowadays but if you give clear responses to his posts explaining where you're at as explained above then he too can help.

Remember, we don't have your work in front of us. We rely 100% on what you tell us mixed with what we know can & cannot be true. Without that information expressed clearly and accurately we are very limited in how we can help.
3 Weeks Ago #14

Rabbit
Expert Mod 10K+
P: 12,349
What I'm trying to get at is that you should not use this N'$?' because you're telling it to treat the parameter as a literal string. It's a bad habit because it will cause security issues.

You should just use ? like you do for the other parameter. You just need to figure out in Oracle EDQP where you identify the data types of the parameters outside of the SQL code itself.

In pseudo-code, the correct and secure way to do this is this:
Expand|Select|Wrap|Line Numbers
  1. set parameter 1 data type = integer
  2. set parameter 2 data type = Unicode string
  3. run sql "insert into Selva_temp_nvarchar_05_23_2019 (SZEDLN, SZDSC) values (?,?)"
3 Weeks Ago #15

Post your reply

Sign in to post your reply or Sign up for a free account.