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.
| Author |
Topic |
|
lunarsx
Starting Member
2 Posts |
 Posted - 2002-11-11 : 12:59:25
|
| Hello! I'm hoping someone can help me with an apostrophe question. Before I start, I believe it has something to do with a setting, rather than the actual sql statement itself. I've known, since I started programming SQL statements within ASP pages, to double up on apostrophes. My problem is with searching the database. If I open the table using the Enterprise Manager and run a query I get no results. This is USING the doubling up of apostrophes. For instance, in my table there is a field called articletext. It is datatype NTEXT, and all text in the field is lowercase. (this is SQL 7 btw)When I search for BRENNER'S and use the following I get no results:SELECT * FROM articles WHERE articletext LIKE '%brenner''s%'When I use this query... I get results:SELECT * FROM articles WHERE articletext LIKE '%brenner%'When I use this query... I get the usual error as expected:SELECT * FROM articles WHERE articletext LIKE '%brenner's%'BRENNER'S is listed in numerous articles, yet I can't get them to show up in a query. And, like I mentioned, this happens even if I directly access the table to run the query.It sounds like a setting problem to me, but I don't recall there being a setting for this. Any help would be greatly appreciated. Thanks!Pete |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2002-11-11 : 14:47:06
|
| You're passing the ' correctly in ASP. The bug may be in your stored procedure??This article may help. You may need to double up the ' a second time inside the SP?[url]http://sqlteam.com/item.asp?ItemID=293[/url]Sam |
 |
|
|
lunarsx
Starting Member
2 Posts |
Posted - 2002-11-11 : 15:26:54
|
| I think I have it figured out... can someone else test to be sure??this type of query does NOT work on NTEXT field types.I tested with NVARCHAR, VARCHAR, and CHAR and all produced results.NTEXT produced No Results.Does anyone know if this is by design? Isn't there an 8,000 characeter limit other field types?? I need a way to store these articles in my system, but not be held back by character limits.Thanks for the help.Pete |
|
|
|
KHeon
Posting Yak Master
135 Posts |
Posted - 2002-11-11 : 16:04:22
|
| I believe you are correct, in that you cannot do a LIKE lookup on an NTEXT (or TEXT) datatype in SQL 7. It is possible to do a LIKE against a TEXT datatype in SQL 2000 however (I know, I've done it, much to my amazement).The reason, if I had to guess is because TEXT and NTEXT datatypes are BLOB (binary large object) datatypes which can hold upwards of 2GB of data. Think of the overhead involved in doing a "LIKE" search against that much data, especially in a BIG (lots of rows) table.In SQL 2000 you can have a varchar up to 8k but the max row size itself cannot exceed 8k, in SQL 7 you are limited to 255 for the varchar type.If searching large columns of text is a necessity you might want to look into setting up Full-Text Search on your server, and setting the columns appropriately.Good luck!Kyle HeonPixelMEDIA, Inc.Senior Application Programmer, MCPkheon@pixelmedia.com |
|
|
|
|
|
|
|
|
Apostrophes and No Results