Replace Single Quote to Double quotes in a String

[SelvamaniTaurus]

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.

[Rabbit]

What code have you tried so far?

[SelvamaniTaurus]

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.

[Rabbit]

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.

[SelvamaniTaurus]

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

[Rabbit]

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?

insert into Selva_temp_nvarchar_05_23_2019
 (SZEDLN, SZDSC)
 values
 (?,?);

[SelvamaniTaurus]

If I load with just (?,?) it loads everything but the foreign characters are replaced with ? in the target table.

[Rabbit]

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:

cmd.Parameters.Add(
                new SqlParameter("@name",
                SqlDbType.NVarChar, 100) {
                Value = "ŞİĞİL"
            });

[NeoPa]

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.

REPLACE([YourString],'''','''''')

[SelvamaniTaurus]

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.

[SelvamaniTaurus]

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.

[NeoPa]

And, just to be clear, do you feel you can do that now? Or do you feel stuck somewhere still?

[SelvamaniTaurus]

I'm stuck with it still. Thanks for asking.

[NeoPa]

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.

[Rabbit]

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:

set parameter 1 data type = integer
set parameter 2 data type = Unicode string
run sql "insert into Selva_temp_nvarchar_05_23_2019 (SZEDLN, SZDSC) values (?,?)"