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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-05-23 : 08:08:48
|
| Alan Notton writes "I have read your articles concerning apostrophes and they don't give me the answer that I seek. In fact, I haven't seen this question properly answered anywhere yet, and I'm starting to think that there is no solution. Here goes:-I have got a database which contains loads of book titles. Many of these book titles contain apostrophes, i.e. Harry Potter's Adventures. On my form, there is a search box where the visitor can type in the name of the book that they are seeking. If they type in, Harry Potters Adventures, without the apostrophe, i would like the SQL Select statement to capture the Harry Potter's Adventures title contained within the database.Select statement is currently like this and doesn't do what I want it to do for obvious reasons (TitleBox is the value from the form).SELECT * FROM BookDetails WHERE Title LIKE '" & TitleBox & "%'I need to know if there is a piece of code or command which will effectively replace the apostrophes within the BOOKDETAILS part with nothing.I've heard people offer advice like double-up on the apostrophes in the TitleBox value from the form. This is NOT what I want to hear. I want to know if the BOOKDETAILS variable can be 'doctored' in some way so that it will effectively REPLACE all apostophes with nothing, i.e. Replace(BookDetails,"'","").If you can solve this problem for me, even if it's something like "there is no feature like this available within ASP/SQL" then at least I'd know and will be grateful. Thank you.Yours (very hopefully)Alan" |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-05-23 : 08:10:55
|
| something likeSELECT * FROM BookDetails WHERE replace(Title, '''', '') LIKE replace('" & TitleBox & "', '''', '') + ''%'''==========================================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. |
 |
|
|
|
|
|
|
|
Apostrophe problem - reversed??