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
>
