|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
String RICHARD O'BRIAN in WHERE ClauseHi
I'm sure this is probably an elementary question, but I'm finding it difficult to search for a name with an apostrophe in it, for example the name in the Subject Header : O'Connel/O'Brian/O'Reily etc.... I have tried the obvious WHERE fname = 'O'Brian' but that does not work, since it encapsulates the O as a string on its own? Double quotes do not seem to work either? Any suggestions? Kind Regards Ricky How about
WHERE fname = 'O''Brian' Cheers, JP (Just a programmer;)) Show quote "MS News" <m**@mis.com> wrote in message news:uF2RIzXqFHA.3064@TK2MSFTNGP15.phx.gbl... > Hi > > I'm sure this is probably an elementary question, but I'm finding it > difficult to search for a name with an apostrophe in it, for example the > name in the Subject Header : O'Connel/O'Brian/O'Reily etc.... > > I have tried the obvious WHERE fname = 'O'Brian' > > but that does not work, since it encapsulates the O as a string on its > own? > Double quotes do not seem to work either? > > Any suggestions? > > Kind Regards > > Ricky > > > I have tried the obvious WHERE fname = 'O'Brian' Did you try doubling up the apostrophe?WHERE fname = 'O''Brian' Thanks JP & Aaron.
The Double Apostrophe worked just fine. I don't suppose you guys would know how to make this dynamic. Since I have a table full of 1000+ names, which I use in the another query. Is it possible to checkt the name and see if there is an apostrophe and then add another? Kind Regards Ricky Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:eq04h0XqFHA.3404@TK2MSFTNGP11.phx.gbl... > > I have tried the obvious WHERE fname = 'O'Brian' > > Did you try doubling up the apostrophe? > > WHERE fname = 'O''Brian' > > > I don't suppose you guys would know how to make this dynamic. Since I I don't quite understand what you think that will accomplish. Are you > have > a table full of 1000+ names, which I use in the another query. Is it > possible to checkt the name and see if there is an apostrophe and then add > another? saying you want to update the table and save an extra apostrophe in all the data values? This is absolutely the wrong approach. The second apostrophe acts as an escape mechanism. When you search for 'O''Brian' you will find the value O'Brian (one apostrophe). The doubling up belongs in the string passed to the query, not in the data! > I don't suppose you guys would know how to make this dynamic. Since I Have you considered using a JOIN... then you don't have to worry about > have > a table full of 1000+ names, which I use in the another query. pulling the name out and stuffing it back in, which is where the delimiter problem comes in. Show us what your tables look like, the data that is in them, and the query you are trying to run... http://www.aspfaq.com/5006 A With out going into why you want to do that ... :)
How about select replace(fname,'''','''''') from xYZ -- Show quoteCheers, JP (Just a programmer;) ------------------------------------------------------------------ A program is a device used to convert, data into error messages ------------------------------------------------------------------ "MS News" <m**@mis.com> wrote in message news:%23$HXiIYqFHA.2432@TK2MSFTNGP10.phx.gbl... > Thanks JP & Aaron. > > The Double Apostrophe worked just fine. > > I don't suppose you guys would know how to make this dynamic. Since I > have > a table full of 1000+ names, which I use in the another query. Is it > possible to checkt the name and see if there is an apostrophe and then add > another? > > Kind Regards > > Ricky > > > "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in > message > news:eq04h0XqFHA.3404@TK2MSFTNGP11.phx.gbl... >> > I have tried the obvious WHERE fname = 'O'Brian' >> >> Did you try doubling up the apostrophe? >> >> WHERE fname = 'O''Brian' >> >> > >  |
|||||||||||||||||||||||
Other interesting topics