Hi,

Have a programmer thats allowed users to enter "apostrophe" is a name field.
Im attempting to search of them using:

select * from table where [name] like '% ' %'

i.e

' % ' % ' (with no spaces)

and am hitting the obvious problem that the second "apostrophe" is seen as
the end of the string. Can anyone with a solution ?

Thanks for your time
Scott

You have to use two apostrophes in the search pattern, like '%''%'.

SELECT x
FROM (SELECT 'O''Conner'
UNION
SELECT 'Conner') AS T(x)
WHERE x LIKE '%''%';

--
Plamen Ratchev
http://www.SQLStudio.com

Scott
create table #t (c varchar(50))

insert into #t values ('John')

insert into #t values ('O''Brian')



select * from #t

where c like '%''%'





"Scott" <scott_lotus@yahoo.co.uk> wrote in message
news:OylGtJdDJHA.4800@TK2MSFTNGP06.phx.gbl...
> Hi,
>
> Have a programmer thats allowed users to enter "apostrophe" is a name
> field. Im attempting to search of them using:
>
> select * from table where [name] like '% ' %'
>
> i.e
>
> ' % ' % ' (with no spaces)
>
> and am hitting the obvious problem that the second "apostrophe" is seen as
> the end of the string. Can anyone with a solution ?
>
> Thanks for your time
> Scott
>