MS SQL Server - Simple question about a SELECT statement

Author

Simple question about a SELECT statement

Steen Persson

2005-07-21, 1:23 pm

Hi

Could somebody please help me with the syntax searching for a string
that contains a ' in a text field? It should be very simple, but I
can't get it working...

Regards
Steen

David Gugick

2005-07-21, 8:23 pm

Steen Persson wrote:
> Hi
>
> Could somebody please help me with the syntax searching for a string
> that contains a ' in a text field? It should be very simple, but I
> can't get it working...
>
> Regards
> Steen

create table #test(col1 varchar(100))

insert into #test values ('ABC')
insert into #test values ('123')
insert into #test values ('''ABC')
insert into #test values ('ABC''')
insert into #test values ('AB''C')

Select * from #test

Select * from #test where col1 LIKE '%''%'

col1
----
'ABC
ABC'
AB'C

drop table #test

--
David Gugick
Quest Software
www.imceda.com
www.quest.com

Hari Prasad

2005-07-21, 8:23 pm

Hi,

Use the wildcard charecter %.

Sample:-

Select field from Table where textcol like '%searchtext%'

Thanks
Hari
SQL Server MVP

"Steen Persson" <spe@REMOVEdatea.dk> wrote in message
news:usiTKPijFHA.3544@TK2MSFTNGP15.phx.gbl...
> Hi
>
> Could somebody please help me with the syntax searching for a string that
> contains a ' in a text field? It should be very simple, but I can't get it
> working...
>
> Regards
> Steen

Hugo Kornelis

2005-07-21, 8:23 pm

On Thu, 21 Jul 2005 20:41:15 +0200, Steen Persson wrote:

>Hi
>
>Could somebody please help me with the syntax searching for a string
>that contains a ' in a text field? It should be very simple, but I
>can't get it working...
>
>Regards
>Steen

Hi Steen,

This will find the rows you request if the data type is char, nchar,
varchar, or nvarchar. Or if the data type is text or ntext and the ' is
within the first 8000/4000 characters of the text:

SELECT SomeColumn
FROM SomeTable
WHERE SomeColumn LIKE '%''%'

If you need to search text / ntext columns and the ' may be beyond the
first 8000/4000 characters, you'll have to use a cursor to loop through
the table, then use the text handling functions to retrieve and examine
the string in 8000-byte portions.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Steen Persson

2005-07-21, 8:23 pm

David Gugick wrote:
> Steen Persson wrote:
>
>
>
> create table #test(col1 varchar(100))
>
> insert into #test values ('ABC')
> insert into #test values ('123')
> insert into #test values ('''ABC')
> insert into #test values ('ABC''')
> insert into #test values ('AB''C')
>
> Select * from #test
>
> Select * from #test where col1 LIKE '%''%'
>
> col1
> ----
> 'ABC
> ABC'
> AB'C
>
> drop table #test
>
>
>
>
Thanks David....I knew it was very simple. I fumbled around with ' and %
but couldn't get the right combination....:-)

Regards
Steen

Sponsored Links