Select where name has an apostrophe

Question

Post reply

Select where name has an apostrophe

firstrate

Old Hand

Points: 379
More actions
September 20, 2004 at 3:23 pm

#60521

I hope I'm posting this in the right place......
I am trying to find out how many accounts have a name that contain an apostrophe.
So I try:
select * from table1 where name like '%'%'
But of course this is not correct, since it sees it as an unclosed quotation mark. Any ideas on how to find accounts that include an ' anywhere in the name?
Thanks for any thoughts!

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

Escape it by doubling them up.

select * from table1 where name like '%''%'

michaela SSCertifiable Points: 5649 More actions · Answer 2

SET QUOTED_IDENTIFIER off

select * from table1 where name like "%'%"

This should work fine.

sukhoi971 SSCarpal Tunnel Points: 4833 More actions · Answer 3

select * from table1 where col1 like '%''%'

this will do it

<a href="http://www.websolsoftware.com"> For IT jobs click here</a>

*Sukhoi*[font="Arial Narrow"][/font]

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 4

Yet another method (without having to keep track of escaped quotes)

select foo from bar where charindex(char(39), foo) > 0

/Kenneth

Tom Williams-175034 SSC Enthusiast Points: 103 More actions · Answer 5

Or to take Kenneth's use of char(39), how about:

SELECT Bob FROM Dog WHERE Bob LIKE '%' + CHAR(39) + '%'

Pretty much the same thing really - useful when you need to build a string with quotes in it.

firstrate Old Hand Points: 379 More actions · Answer 6

Thanks everyone for the replies! I will try them out..

WILSON ROBERTO LEAL DO COUTO Newbie Points: 9 More actions · Answer 7

WILSON ROBERTO LEAL DO COUTO

Newbie

Points: 9

September 21, 2004 at 7:22 am

#523800

try it

select * from table1 where name like '%''%'

Kathy Collins SSC Enthusiast Points: 141 More actions · Answer 8

I tried this and it works. you need two single quotes in the middle, instead of one double quote (didn't seem to work for me with one double quote.) I think that's what others said, but this font made it look like a double quote.

select * from table1

where [name] like '%' '%'

firstrate Old Hand Points: 379 More actions · Answer 9

I now have another question:

How do you insert data into a table that contains an apostrphe? I'm using sql 2000. I know that in sql7 I could use double quotes, but it is not working in sql 2000.

Thanks for any suggestions

firstrate Old Hand Points: 379 More actions · Answer 10

Found the answer to my own question. Should have searched longer.....

insert into table1 values ('abc''s','x')

record will read:

column1 abc's

column2 x

Farrell Keough SSCoach Points: 17414 More actions · Answer 11

If you are doing this from an application, I would recommend masking it. In other words, your application will display an apostrophe [ CHAR(39) ], but you can use the REPLACE function to change that character to ` [ CHAR(96) ] for the actual data within your database. Then you will not have any problems with switching back and forth. And, even if someone forgets to switch, that character will still read okay, albiet read a bit odd...

I wasn't born stupid - I had to study.

babuganesh2000 Hall of Fame Points: 3123 More actions · Answer 12

create table ##mytable(name varchar(25))

insert ##mytable

select 'Manisha'''

insert ##mytable

select 'Man''isha'

select * from ##mytable

Thanks,

Ganesh