Escape Chracters - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Escape Chracters

Author

Topic

filipinoheat
Starting Member

8 Posts

Posted - 2006-07-26 : 14:22:27

Does SQL Server 2000 provide any escape chracters?

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-26 : 14:52:09

In what context?
You can specify your own escape character with some operators e.g. like.

You can escape a single quote in a string via a single quote.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-07-26 : 14:52:57

instead of using something like 'quoted text' use '''quoted text'''

-ec

filipinoheat
Starting Member

8 Posts

Posted - 2006-07-26 : 15:01:44

Im trying to update this code set of html code into one of my tables:

 BVIG Financial Frequently Asked Questions 
 <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p> <o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<H4 class=MsoNormal style="MARGIN: 0in 0in 0pt"><o:p></o:p>

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-07-26 : 15:50:20

try triple quoting your text like in my example above.

-ec

Kristen
Test

22859 Posts

Posted - 2006-07-27 : 05:18:35

Hang on a mo ...

What problem have you actually got with the UPDATE?

If you do a REPLACE then you won't have a problem - double up any single quotes in the find & replacement strings.

If you are replacing whatever is already there with some new text then put it in the SET statement, and again double-up the single quotes.

But as there are NO single quotes in the example you have posted I wonder if the problem is more serious.

If this data is coming from some front end stuff and you have NOT taken care of embedded quotes you should either:

1) User parameterized queries
2) Double-up the single quotes.

I'm not meaning this particular front-end to back-end query, I'm meaning all of them!

Otherwise you will be leaving yourself wide open to SQL+Injection

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-27 : 05:24:40

Are you using " as a string delimiter? If so then you should use '.
With that you shouldn't have a problem in sql server.

If you are then you are probably mistaken as to where the problem is i.e. nothing to do with sql server.
Maybe you are using a product which uses " as a string delimiter - in which case you need to deal with however that product escapes a " - maybe "" but it will depend on the product.

Think you need to give a bit more info about what you are doing rather than trying to guess where the problem lies.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

filipinoheat
Starting Member

8 Posts

Posted - 2006-07-27 : 13:17:28

I'm sorry if I'm not being clear but this is what I'm trying to do. I'm trying to update a table with new descriptions inside an html tags. I'm using query analyzer to query the table, but unfortunately it does not display the full result. So to resolve this issue is that I export result into notepad by using bcp utility and modify the things that I need, copy and paste it onto my update query like this:

update myTable
set faq_text = ' BVIG Financial Frequently Asked Questions 
 <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p> <o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<H4 class=MsoNormal style="MARGIN: 0in 0in 0pt"><o:p></o:p>'

But this gives me an error since the html tag attributes are using single quotes. I thought T-SQL would have escape chracters like C#.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-07-27 : 13:24:35

>>I'm using query analyzer to query the table, but unfortunately it does not display the full result.

Don't know if this will solved you problem but try:
tools | options | Results tab | Maximimum characters per column: 8000

EDIT:
you can also use REPLACE to change single quotes to two single quotes. A single quote in front of another single quote acts as an "escape" character for the 2nd single quote.

Be One with the Optimizer
TG

Kristen
Test

22859 Posts

Posted - 2006-07-27 : 15:01:24

"But this gives me an error since the html tag attributes are using single quotes."

I don't get it - there are no single quotes in your example, only double quotes (well, apart from the terminators at each end of the string of course)

Not sure what you are working on, but in case it is of any help:

The HTML tags are a bit unbalanced too - e.g. seven "" at the start which aren't closed (nor is the <H4> closed towards then end) and lots of empty tags -like:


<P class=MsoNormal style="MARGIN: 0in 0in 0pt; TEXT-ALIGN: center">
	<SPAN style="FONT-SIZE: 14pt; mso-bidi-font-size: 12.0pt; mso-bidi-font-style: italic">
		<o:p>
			<FONT face=Arial size=1>
			</FONT>
		</o:p>
	</SPAN>
</P>

Also these guys are mis-blanced:


<P class=MsoTitle style="MARGIN: 0in 0in 0pt">
	<STRONG>BVIG Financial Frequently Asked Questions<BR></STRONG>
	<STRONG>
	</P>
</STRONG>

Kristen

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-28 : 05:48:20

quote:
Originally posted by filipinoheat

But this gives me an error since the html tag attributes are using single quotes. I thought T-SQL would have escape chracters like C#.

I actually created one table and applied your update statement there to check for the problem...It's working fine!

What's your problem? Be specific?

Harsh Athalye
India.
"Nothing is Impossible"

filipinoheat
Starting Member

8 Posts

Posted - 2006-07-28 : 17:32:42

It messes up when the tags contain a passage with an apostrophe. For instance if i update with this:

update my_table
set column = 'Hello World wohoooooooo Break's'

how do i tell sql to ignore that apostrophe in Break. I'm using query analyzer to do it.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-28 : 19:00:40

update my_table
set column = 'Hello World wohoooooooo Break''s'

Or has that already been suggested?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-07-29 : 00:04:09

quote:
Originally posted by nr

update my_table
set column = 'Hello World wohoooooooo Break''s'

Or has that already been suggested?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

yep, been suggested (by me)...but I don't think "it took".

Be One with the Optimizer
TG

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-29 : 13:31:44

twas sarcasm.
The first three responses all mention it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Ashtom
Starting Member

1 Post

Posted - 2007-03-09 : 06:05:17

I have problem to input double quotes through my query in the Query Analyzer. I input the double quotes as " but on my HTML page, it outputs as & #34. Is there any escape characters (or any magical trick) to make my double quotes to be displayed as " and not as & #34 ?

Thanks in advance for your help

PS: I added a space character between the & and #34 so that it's not displayed as #34.

[edit] well it seems that this problem is actually not an SQL problem but more ASP problem since it seems that a script is formatting the string before outputting it on the HTML page, sorry.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources