September 25, 2008 at 12:58 pm
My SQL statements crash when I want to select where a string value equals something that contains an apostrophe ('). I.e the name "O'Conner".
What is the simplest way to get around these problems? I could either do it client side in asp or server side in SQL, or thirdly (and maybe I prefer this way), by simply removing all instances of (') from my database, so "O'Conner" becomes "O Conner".
How can I write an SQL query to find and replace all (') with ( ) ?
September 25, 2008 at 1:07 pm
replace(b.[Name],'''','''''')
September 25, 2008 at 1:59 pm
Is the first argument the table name? The database name?
Will that replace every single instance of the " ' "
Replace ( [Table Name Here] , ''', ' ')
September 25, 2008 at 2:15 pm
Gladly never had this problem
Could SET QUOTED_IDENTIFIER fix it?
http://msdn.microsoft.com/en-us/library/ms174393.aspx
September 25, 2008 at 3:41 pm
Ok I am getting close, I have managed to select all of the records that have the apostraphe, using
like '%''%'
select * from (TABLE NAME) where (FIELD NAME) like '%''%'
Now I just need to remove the apostraphes and replace them with spaces in that selection.....
September 26, 2008 at 7:19 am
See how single quote works in sql server
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx
Failing to plan is Planning to fail
September 29, 2008 at 6:40 am
Madhivanan (9/26/2008)
See how single quote works in sql serverhttp://sqlblogcasts.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx
Well thats just stupid, why would I write code to specificaly find and replace all instances of single quotes with double quotes in my query strings everytime I want to send them to SQL Server?
No, much better to get rid of the apostraphes from the outset I do not need them at all.
"O'Conner" becomes "O Conner", that is a much better solution for my purpose. How do I do this?
September 29, 2008 at 7:00 am
garethmann101 (9/29/2008)
No, much better to get rid of the apostraphes from the outset I do not need them at all.
"O'Conner" becomes "O Conner", that is a much better solution for my purpose. How do I do this?
Why don't you get rid of all the vowels from the names as well as the apostrophes, it will reduce your storage requirements and let's face it, we can more or less guess what the vowels will be and where they are supposed to go...
You've missed the point, Gareth. "O Conner" is most certainly not the same as "O'Conner". Try telling someone having that surname - just as your surname is Mann and not Mn, and your forename is Gareth and not Garth. They're not the same.
In our shop, anyone caught deliberately corrupting the data out of coding idleness would be frogmarched out of the door. And then sued.
What was the question again? 😉
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 29, 2008 at 7:08 am
You started with a question about finding apostrophes in string expressions.
You've got several suggestions. Did you try them?
You continues then with a question about finding apostrpohes in field names. Field Names are not quiet string expressions. While having apostrpohes in data is valid case, having any special symbols (or reserved words) in field names is unacceptable. I would assume that in this case scenario you should simply use a simple naming convention. BTW, using spaces in table names is also not recommended.
I would also assume that advices that you are receiving here are not "stupid". You have to formulate your question thorougly to receive a professional advice.
September 29, 2008 at 7:20 am
Ok, did not want to offend any Irish people here, sorry for that.
And yes it is my fault for not making it clear at the beginning that I wanted to only know how to find and replace the apostraphes, not how to make the apostraphes work in the query string. I said "maybe" I would prefer to do it this way, I should have made it clear in a later post that at some point I had decided to definately do it this way.
My database has no people's names in it at all. I used O'Conner as an example of a string that everybody would recognize and be familiar with straight away.
Anyway, I will no longer be searching by a text field, I will be always searching using an ID field instead, that way I can keep the apostraphes in the field in question and just pass the ID values into my queries instead, which I would imagine many more expereinced SQL database people would approve of for a whole plethora of other reasons.
But regardless of that, having the ASP web code iterate through every character of a string field (varying in length from about 20-80 characters) checking for apostraphes and replacing them with double quotes, everytime a search is done would definately waste CPU, especially when there are only about 100 out of 100,000 records that even have the apostraphes. For the other 99,900 records, it would be iterating through each character and finding no apostraphes, would you agree that that would be a waste of time?
Your vowel example is not a good one because every single record, not simply 1 % of the records, have vowels.
September 29, 2008 at 7:29 am
Gareth, how about posting a real example of a statement which would cause a crash as you describe? There are numerous ways of dealing with embedded single quotes - Madhivanan has some good suggestions in his blog - and I'm sure you would have a solution to your original post in no time at all.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 29, 2008 at 7:32 am
You've got several suggestions. Did you try them?
Yes, I am still stuck on getting the find and replace to work, see my last post from last Thursday (Sept. 25th).
You continues then with a question about finding apostrpohes in field names. Field Names are not quiet string expressions.
Sorry for any confusion, the field names do not have apostraphes, it is the value in the fields.
I would also assume that advices that you are receiving here are not "stupid". You have to formulate your question thorougly to receive a professional advice.
Yes, you are right, I did not make clear how my thought process on solving the problem had evolved over the course of the lats few days. When I said that that is "stupid" it was just my frustration that people were trying to solve a different problem (changing the queries in the ASP code) rather than doing a find and replace in the database. But I should have expected that I guess, seeing as my original question was rather open ended in terms of what solution I should seek.
October 3, 2008 at 4:01 am
garethmann101 (9/25/2008)
My SQL statements crash when I want to select where a string value equals something that contains an apostrophe ('). I.e the name "O'Conner".
What is the simplest way to get around these problems? I could either do it client side in asp or server side in SQL, or thirdly (and maybe I prefer this way), by simply removing all instances of (') from my database, so "O'Conner" becomes "O Conner".
How can I write an SQL query to find and replace all (') with ( ) ?
Is this you want?
declare @name varchar(100)
set @name='O''Conner'
select @name as name, replace(@name,'''','') as name_no_quote
Failing to plan is Planning to fail
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply