468,257 Members | 1,429 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,257 developers. It's quick & easy.

Replace Single Quote to Double quotes in a String

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.
May 23 '19 #1
14 4184
Rabbit
12,511 Expert Mod 8TB
What code have you tried so far?
May 23 '19 #2
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.
May 23 '19 #3
Rabbit
12,511 Expert Mod 8TB
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.
May 23 '19 #4
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
May 23 '19 #5
Rabbit
12,511 Expert Mod 8TB
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.  (?,?);
May 23 '19 #6
If I load with just (?,?) it loads everything but the foreign characters are replaced with ? in the target table.
May 24 '19 #7
Rabbit
12,511 Expert Mod 8TB
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.             });
May 24 '19 #8
NeoPa
32,060 Expert Mod 16PB
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],'''','''''')
May 26 '19 #9
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.
May 27 '19 #10
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.
May 27 '19 #11
NeoPa
32,060 Expert Mod 16PB
And, just to be clear, do you feel you can do that now? Or do you feel stuck somewhere still?
May 28 '19 #12
I'm stuck with it still. Thanks for asking.
May 28 '19 #13
NeoPa
32,060 Expert Mod 16PB
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.
May 28 '19 #14
Rabbit
12,511 Expert Mod 8TB
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 (?,?)"
May 28 '19 #15

Post your reply

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

Similar topics

5 posts views Thread by sinister | last post: by
9 posts views Thread by Dynamo | last post: by
7 posts views Thread by Leif B. Kristensen | last post: by
5 posts views Thread by Joel | last post: by
1 post views Thread by Randy | last post: by
3 posts views Thread by Roger Helliwell | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by kermitthefrogpy | last post: by
reply views Thread by zattat | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.