June 16, 2015 at 11:19 am
A table had values like NOTRPT'D ( Wjhat I mean is there can be one or more single quotes in the string
What I need is an expression that will convert every single one of those single quotes to a '' ( Which means 2 single quotes )
So if the string is ABC'DEF'GHI
I want the output to be ABC''DEF''GHI
Note: You may have many single quotes in different positions
June 16, 2015 at 11:29 am
Notice that when including a quote ' with quotes '', you need to double it up.
REPLACE( Column1, '''', '"' )
To make it more readable, you can also specify char(39) in place of ' character like so:
REPLACE( Column1, char(39), '"' );
Or this:
REPLACE( Column1, char(39), char(34) );
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 16, 2015 at 11:36 am
It's a little tricky to demonstrate because you have to escape the single quote in the example itself but the execution is fairly simple.
DECLARE @quotedString NVARCHAR(10) = 'ABC''DEF''GHI'
SELECT REPLACE(@quotedString,'''','"');
June 16, 2015 at 11:37 am
Thanks
You were close..
It should be REPLACE( EGFR_value, CHAR(39) , '''''' )
A total of 6 quote are in the expression
June 16, 2015 at 11:55 am
6 single quotes does work as a replacement string because...
'<-Outside quotes contain your string->'
The 2nd & 3rd '' will become a single quote because the 2nd is used to escape the 3rd.
The 4th & 5th '' will become a single quote because the 4th is used to escape the 5th.
In my example I just used a double quote character which works just the same.
Out of curiosity why do you want to replace single quotes. Normally these lines of questions are because people need to preserve the single quote when working with the data. I wouldn't recommend changing the data just so some code won't crash. It should be the other way around.
Cheers
June 16, 2015 at 5:19 pm
yb751 (6/16/2015)
6 single quotes does work as a replacement string because...'<-Outside quotes contain your string->'
The 2nd & 3rd '' will become a single quote because the 2nd is used to escape the 3rd.
The 4th & 5th '' will become a single quote because the 4th is used to escape the 5th.
In my example I just used a double quote character which works just the same.
Out of curiosity why do you want to replace single quotes. Normally these lines of questions are because people need to preserve the single quote when working with the data. I wouldn't recommend changing the data just so some code won't crash. It should be the other way around.
Cheers
Actually that, I think, is why 6 single quotes work while using a double quote doesn't! You appear to have missed the text " ( Which means 2 single quotes )" in the original post, which effectively says "I want to get two single quotes from one".
Tom
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply