472,794 Members | 4,741 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,794 software developers and data experts.

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,
May 23 '19 #1
14 6525
12,516 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
12,516 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

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
12,516 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
  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
12,516 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
32,534 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

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
32,534 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
32,534 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
12,516 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

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

Similar topics

by: sinister | last post by:
The examples in the online manual all seem to use double quotes, e.g. at http://us3.php.net/preg_replace Why? (The behavior is different with single quotes, and presumably simpler to...
by: Dynamo | last post by:
Hi, I am still confused as when to use single or double quotes. This works: echo "<td>" . $row . "</td>"; and this does not
by: Leif B. Kristensen | last post by:
I'm working with a Python program to insert / update textual data into a PostgreSQL database. The text has single and double quotes in it, and I wonder: What is the easiest way to escape quotes in...
by: girish | last post by:
In my XML document, some node attributes data contains both single quot and double quote characters, such as <input msg="Hello "World", What's up"/>. The double quotes are in form of escape...
by: Joel | last post by:
Hi, I incorporated a function in my code that whenever I use a string variable in an sql statement if the string contains a single quote it will encase it in double quotes else single quotes. ...
by: Steve | last post by:
How do I get a single quote (') in a NVARCHAR string in MS SQL Server? e.g. SELECT @strsql = "SELECT * FROM tblTest WHERE Field1 Like 'blah'' Obviously this is invalid as the single quote...
by: Daniel Lim | last post by:
When using XmlSerializer, I notice that it does not normalize the single quote and double quote characters, i.e. does not change ' to &apos; and " to &quot. However, it does normalize other...
by: Randy | last post by:
After searching this forum, I think I've got delimiters right for single quotes. but I'm still lost on double quotes. Here's what I'm after: Update .... set mytext = 'O''Brien said, "I inserted...
by: Roger Helliwell | last post by:
Hi Everyone, I have a DataGrid which contains various columns, one of which is an image which comes from the data source. I'd like to bind the HTML 'alt' setting as well with text from the data...
by: iceley11 | last post by:
Hi, I'm simply trying to put a formula in a range of cells in excel using VBA. I have the formula below: Sub TimeSpentFormula() Range("SR_TimeSpent").Formula = _ ...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: Mushico | last post by:
How to calculate date of retirement from date of birth
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.