Replacing characters in SQL

I am incorporating a perl script loading data into my SQL Server. If I receive a message with a single backslash \ I know to replace it with a double backslash \\. But what if it is a " double quote what do I need to do to get it to appear as is?
ThanksYou'll love this answer: It depends.

Based on your previous message, you might have chosen to change the settings of QUOTED_IDENTIFIER. If you have changed it, then from Perl there is no way to pass a true double quote anymore. If you have not changed it, you don't need to do anything at all to a double quote.

My suggestion would be to leave the quoted identifier off, then use a simple s/'/''/gto fix up any apostrophes in the strings comming from Perl to SQL Server.

-PatP|||Thanks for you help.

-Laurasql

Replacing a single quote

How can use the REPLACE function to replace a single quote in a string in
T-SQL? I have tried using double quotes, but cannot get it to work. I am
trying to replace all the single quotes in at text field with a question
mark. for instance:
REPLACE(myfieldwithquotes,"'",'?')
--
JasonUse two single quotes:
REPLACE(myfieldwithquotes,'''','?')
ML
http://milambda.blogspot.com/|||escape single quote with single quote
replace('''','?')
--
-Omnibuzz
--
Please post ddls and sample data for your queries and close the thread if
you got the answer for your question.
"JasonDWilson" wrote:

> How can use the REPLACE function to replace a single quote in a string in
> T-SQL? I have tried using double quotes, but cannot get it to work. I am
> trying to replace all the single quotes in at text field with a question
> mark. for instance:
> REPLACE(myfieldwithquotes,"'",'?')
> --
> Jason|||That is so you can escape them
REPLACE(myfieldwithquotes, CHAR(39), CHAR(39)+CHAR(39))
or to replace with a blank space
REPLACE(myfieldwithquotes, CHAR(39), '')sql

REPLACE function needed to remove single quotes from an output

I have a variable @.var1 of type varchar(100) which captures the following
error message, which is produced by executing a sql statement:
Incorrect syntax near the keyword 'where'.
Please pay special attention to the single quotes around the word where.
These single quotes are causing a lot of problems when I include the above
error message as-is in XML processing.
If I remove both the above single quotes manually, things work fine.
Also, if I change them to two single quotes, things also work fine. But
since this message is captured programatically, I cannot manipulate it in
these two ways.
Alternatively, I decided to remove these two single quotes around the word
where. I tried using the following but did not succeed.
I am using the REPLACE function, which has the following syntax: REPLACE (
'string_expression1' , 'string_expression2' , 'string_expression3' )
So, in my case: select REPLACE(@.var1, '', ' ')
In the above expression, I am basically attempting to replace the single
quotes with a space.
Please note that the string_expression2 is two single quotes.
string_expression3 is a single quote followed by a space and then followed
by single quote.
This statement produces another error: An expression of non-boolean type
specified in a context where a condition is expected, near 'select'.
Basically, if re-run this same replace by changing single quotes to two
single quotes, the above replace statement does not produce an error.
Could somebody help me get a working REPLACE statement which will give me my
original error message free of single quotes in the following form.
Incorrect syntax near the keyword where.
Appreciate your help.
ThanksHB
Can you show us @.var1 ?
"HB" <nospam@.microsoft.com> wrote in message
news:%232SpQF5qFHA.1788@.tk2msftngp13.phx.gbl...
>I have a variable @.var1 of type varchar(100) which captures the following
> error message, which is produced by executing a sql statement:
> Incorrect syntax near the keyword 'where'.
> Please pay special attention to the single quotes around the word where.
> These single quotes are causing a lot of problems when I include the above
> error message as-is in XML processing.
> If I remove both the above single quotes manually, things work fine.
> Also, if I change them to two single quotes, things also work fine. But
> since this message is captured programatically, I cannot manipulate it in
> these two ways.
> Alternatively, I decided to remove these two single quotes around the word
> where. I tried using the following but did not succeed.
> I am using the REPLACE function, which has the following syntax: REPLACE (
> 'string_expression1' , 'string_expression2' , 'string_expression3' )
> So, in my case: select REPLACE(@.var1, '', ' ')
> In the above expression, I am basically attempting to replace the single
> quotes with a space.
> Please note that the string_expression2 is two single quotes.
> string_expression3 is a single quote followed by a space and then followed
> by single quote.
> This statement produces another error: An expression of non-boolean type
> specified in a context where a condition is expected, near 'select'.
> Basically, if re-run this same replace by changing single quotes to two
> single quotes, the above replace statement does not produce an error.
> Could somebody help me get a working REPLACE statement which will give me
> my
> original error message free of single quotes in the following form.
> Incorrect syntax near the keyword where.
> Appreciate your help.
> Thanks
>|||Hello, HB
To remove the single quotes from a string, use:
REPLACE(@.var1, '''', ' ')
(there are 4 quotes as the second parameter; that is because to specify
a single quote in a string, you have to type a quote twice, for
example: 'McDonald''s')
A better idea would be to properly use XML escape sequences for any
special characters, i.e: instead of "<" use "<", instead of a single
quote, use "'", etc. If we consider only the standard special
characters, we can use:
SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
@.var,
'&','&'),'<','<'),'>','>'),'"','"'),'''',''')
But we must also consider that there are accented characters, which
should also be escaped in an XML string, so the best way would be to
construct the XML string using a XML parser (like MSXML), using the
DOM, for example.
Razvan|||Razvan,
Thanks so much for your suggestions.
I am trying to construct the XML string in SQL. Given that SQL is limited
(as far as processing strings) compared to pure programming languages (C++,
C# etc.), what do you suggest I should do to accomplish the escaping of
various characters, including the special ones, like you wrote in your post?
Like I mentioned in my previous post, I am capturing the error in a SQL
stored procedure and want to ship out that error message as a valid (free
from the characters you mentioned) XML string. So, if possible I would like
to accomplish your suggestions in SQL itself. If you can provide help as to
how to achieve escaping various characters using a general purpose approach
(using DOM or otherwise), that will be great.
Reply appreciated
Thanks again Razvan
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1125210516.602885.58280@.g43g2000cwa.googlegroups.com...
> Hello, HB
> To remove the single quotes from a string, use:
> REPLACE(@.var1, '''', ' ')
> (there are 4 quotes as the second parameter; that is because to specify
> a single quote in a string, you have to type a quote twice, for
> example: 'McDonald''s')
> A better idea would be to properly use XML escape sequences for any
> special characters, i.e: instead of "<" use "<", instead of a single
> quote, use "'", etc. If we consider only the standard special
> characters, we can use:
> SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
@.var,
> '&','&'),'<','<'),'>','>'),'"','"'),'''',''')
> But we must also consider that there are accented characters, which
> should also be escaped in an XML string, so the best way would be to
> construct the XML string using a XML parser (like MSXML), using the
> DOM, for example.
> Razvan
>

repair_allow_data_loss Repair statement not processed. Database needs to be in single user

I have to repair a database with repair_allow_data_loss level, so I start
SQL Server on single-user mode with the following command
sqlservr.exe -c -m
and run the repair command:
dbcc checkdb ('blade', repair_allow_data_loss)
I get the following error that doesn't make sense, since I am already in
single user mode:
Server: Msg 7919, Level 16, State 2, Line 1
Repair statement not processed. Database needs to be in single user mode.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Anyone can help?You don't need to start the server in single user mode for the DBCC, just
place the *database* in single-user-mode. You can do this with ALTER
DATABASE:
ALTER DATABASE MyDaabase
SET SINGLE_USER
Note that it's usually better to restore from backup than resort to DBCC
CHECKDB ... REPAIR_ALLOW_DATA_LOSS. However, the option is useful if you
have no backup.
Hope this helps.
Dan Guzman
SQL Server MVP
"savvas" <savvas@.blade.com.cy> wrote in message
news:uI0sdJFLGHA.3944@.tk2msftngp13.phx.gbl...
>I have to repair a database with repair_allow_data_loss level, so I start
> SQL Server on single-user mode with the following command
> sqlservr.exe -c -m
> and run the repair command:
> dbcc checkdb ('blade', repair_allow_data_loss)
> I get the following error that doesn't make sense, since I am already in
> single user mode:
> Server: Msg 7919, Level 16, State 2, Line 1
> Repair statement not processed. Database needs to be in single user mode.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> Anyone can help?
>

repair_allow_data_loss Repair statement not processed. Database needs to be in single

I have to repair a database with repair_allow_data_loss level, so I start
SQL Server on single-user mode with the following command
sqlservr.exe -c -m
and run the repair command:
dbcc checkdb ('blade', repair_allow_data_loss)
I get the following error that doesn't make sense, since I am already in
single user mode:
Server: Msg 7919, Level 16, State 2, Line 1
Repair statement not processed. Database needs to be in single user mode.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Anyone can help?You don't need to start the server in single user mode for the DBCC, just
place the *database* in single-user-mode. You can do this with ALTER
DATABASE:
ALTER DATABASE MyDaabase
SET SINGLE_USER
Note that it's usually better to restore from backup than resort to DBCC
CHECKDB ... REPAIR_ALLOW_DATA_LOSS. However, the option is useful if you
have no backup.
Hope this helps.
Dan Guzman
SQL Server MVP
"savvas" <savvas@.blade.com.cy> wrote in message
news:uI0sdJFLGHA.3944@.tk2msftngp13.phx.gbl...
>I have to repair a database with repair_allow_data_loss level, so I start
> SQL Server on single-user mode with the following command
> sqlservr.exe -c -m
> and run the repair command:
> dbcc checkdb ('blade', repair_allow_data_loss)
> I get the following error that doesn't make sense, since I am already in
> single user mode:
> Server: Msg 7919, Level 16, State 2, Line 1
> Repair statement not processed. Database needs to be in single user mode.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
>
> Anyone can help?
>