I'm not really for sure on how to do this but I need to search a column and replace all of the ' with a ". Is there any way to do that programatically? Thanks,
UPDATE @MyTable SET [MyColumn] = REPLACE([MyColumn], '''', '"');
Try this sample:
DECLARE @MyTable TABLE
(
[MyColumn] VARCHAR(30)
)
INSERT INTO @MyTable
SELECT 'Hello it''s me' UNION ALL
SELECT 'it''s you' UNION ALL
SELECT 'Hello it''s you' UNION ALL
SELECT 'it''s me'
--BEFORE
SELECT * FROM @MyTable
--OUTPUT
--MyColumn
--------------------------------
--Hello it's me
--it's you
--Hello it's you
--it's me
--NOW LETS DO THE UPDATE
UPDATE @MyTable SET [MyColumn] = REPLACE([MyColumn], '''', '"');
--NOW TO TEST IT HAS WORKED
SELECT * FROM @MyTable
--OUTPUT
--Hello it"s me
--it"s you
--Hello it"s you
--it"s me
Dim html as string = "some text from the database here"
dim output as string = "<script type='text/javascript'>printWindow(" & Chr(39) & html & Chr(39) & ");</script>"
The problem is, if there is a ' in the text then it throws a JS error because the actual output looks something like
printWindow('<html><body>the quote at the end of this line throws an error '</body></html>');
I had tried doing a string replace but the vb function can't seem to identify these as '. I used the replace chr(39) but it wasn't replacing them. I copied the value out of the db into an ascii converter and it gave me a return value of 1? It should have
been 39. So I am just replacing all of them with a ". If you have any other suggestions on how to fix this I'm all ears.
I see the problem. I believe there is a different way to escape single quote in JavaScript function - I did use the technique a while back, but forgot how.
I'll try searching somewhere and you can also try in the meantime to google on "escape single quote JavaScript"
Beware of bugs in the above code; I have only proved it correct, not tried it. (Donald Knuth)
Dim html As String = "some text from the ""database here"
Dim output As String = String.Format("<script type='text/javascript'>myFunction('{0}')</script>", html.Replace("""", """).Replace("'", "'"))
I have already tried the visual basic replace. It doesn't catch the ' for some reason. I tried the JS replace also and that didn't work. Like I said, my problem is that the ' is in a strange encoding that the langauges aren't recognizin in the replace function.
You need to escape single quote ' with \' as my samples show. I know it, because I encountered the same problem while back, googled on escape single quote JavaScript and found this answer. It did work for me after I implemented the code shown.
Beware of bugs in the above code; I have only proved it correct, not tried it. (Donald Knuth)
What I am saying is that the replace functions are not catching the ' to replace with the escapse sequence \'. I understand the escaping part, I think that this data must have been copied and pasted from word or something and it is encoded differently because
none of my functions find ' in the string even though it is there.
mandrews1234
Member
335 Points
579 Posts
Replace all single quotes with a double quote
Dec 22, 2009 04:01 PM|LINK
I'm not really for sure on how to do this but I need to search a column and replace all of the ' with a ". Is there any way to do that programatically? Thanks,
Naom
All-Star
36004 Points
7901 Posts
Re: Replace all single quotes with a double quote
Dec 22, 2009 04:15 PM|LINK
There is but why do you want to do this?
Anyway, here is some code
;with cte as (select ID, myColumn from myTable where myColumn LIKE '%' + char(39) + '%' and not myColumn like '%' + char(39) + char(39) + '%')
update cte set myColumn = replace(myColumn, char(39), char(39) + char(39))
(Donald Knuth)
Visit my blog
Microsoft Community Contributor 2011-12
raghav_khung...
All-Star
32835 Points
5563 Posts
MVP
Re: Replace all single quotes with a double quote
Dec 22, 2009 05:00 PM|LINK
You can do like:
UPDATE @MyTable SET [MyColumn] = REPLACE([MyColumn], '''', '"');
Try this sample:
mandrews1234
Member
335 Points
579 Posts
Re: Replace all single quotes with a double quote
Dec 22, 2009 05:01 PM|LINK
Because I am using vb to write this into JS. I.e.
Dim html as string = "some text from the database here"
dim output as string = "<script type='text/javascript'>printWindow(" & Chr(39) & html & Chr(39) & ");</script>"
The problem is, if there is a ' in the text then it throws a JS error because the actual output looks something like
printWindow('<html><body>the quote at the end of this line throws an error '</body></html>');
I had tried doing a string replace but the vb function can't seem to identify these as '. I used the replace chr(39) but it wasn't replacing them. I copied the value out of the db into an ascii converter and it gave me a return value of 1? It should have been 39. So I am just replacing all of them with a ". If you have any other suggestions on how to fix this I'm all ears.
Naom
All-Star
36004 Points
7901 Posts
Re: Replace all single quotes with a double quote
Dec 22, 2009 05:24 PM|LINK
I see the problem. I believe there is a different way to escape single quote in JavaScript function - I did use the technique a while back, but forgot how.
I'll try searching somewhere and you can also try in the meantime to google on "escape single quote JavaScript"
(Donald Knuth)
Visit my blog
Microsoft Community Contributor 2011-12
raghav_khung...
All-Star
32835 Points
5563 Posts
MVP
Re: Replace all single quotes with a double quote
Dec 22, 2009 05:38 PM|LINK
Then try something like this:
Dim html As String = "some text from the ""database here"
Dim output As String = String.Format("<script type='text/javascript'>myFunction('{0}')</script>", html.Replace("""", """).Replace("'", "'"))
Naom
All-Star
36004 Points
7901 Posts
Re: Replace all single quotes with a double quote
Dec 22, 2009 05:42 PM|LINK
I found the answer in another forum. You need to escape ' in JavaScript with \ in front of it.
Here is a C# sample
string x = "I'm a girl"; x = Server.UrlEncode(x).Replace("'", "\\'")); returns: "I\'m+a+girl"(Donald Knuth)
Visit my blog
Microsoft Community Contributor 2011-12
mandrews1234
Member
335 Points
579 Posts
Re: Replace all single quotes with a double quote
Dec 22, 2009 05:46 PM|LINK
I have already tried the visual basic replace. It doesn't catch the ' for some reason. I tried the JS replace also and that didn't work. Like I said, my problem is that the ' is in a strange encoding that the langauges aren't recognizin in the replace function.
Naom
All-Star
36004 Points
7901 Posts
Re: Replace all single quotes with a double quote
Dec 22, 2009 06:00 PM|LINK
You need to escape single quote ' with \' as my samples show. I know it, because I encountered the same problem while back, googled on escape single quote JavaScript and found this answer. It did work for me after I implemented the code shown.
(Donald Knuth)
Visit my blog
Microsoft Community Contributor 2011-12
mandrews1234
Member
335 Points
579 Posts
Re: Replace all single quotes with a double quote
Dec 22, 2009 07:00 PM|LINK
What I am saying is that the replace functions are not catching the ' to replace with the escapse sequence \'. I understand the escaping part, I think that this data must have been copied and pasted from word or something and it is encoded differently because none of my functions find ' in the string even though it is there.