How to construct a like compariso when there are special characts

8 Jun 2006 6:32 PM

bic

How do I construct a like compariso query when there are special characts
such as [ and ] in the string, i.e. [Y] to be searched? Thanks.

--
bic

8 Jun 2006 6:41 PM

Alejandro Mesa

bic,

use a scape character.

Example:

select *
from
(
select 'abc[Y]def' as c1
union all
select 'abc[Y]def' as c1
) as t1
where c1 like '%\[Y\]%' escape '\'
go

AMB

Show quote

"bic" wrote:

> How do I construct a like compariso query when there are special characts
> such as [ and ] in the string, i.e. [Y] to be searched? Thanks.
> --
> bic

8 Jun 2006 6:45 PM

Alejandro Mesa

select *
from
(
select 'abc[Y]def' as c1
union all
select 'abcdef' as c1
) as t1
where c1 like '%\[Y\]%' escape '\'
go

AMB

Show quote

"Alejandro Mesa" wrote:

> bic,
>
> use a scape character.
>
> Example:
>
> select *
> from
> (
> select 'abc[Y]def' as c1
> union all
> select 'abc[Y]def' as c1
> ) as t1
> where c1 like '%\[Y\]%' escape '\'
> go
>
>
> AMB
>
> "bic" wrote:
>
> > How do I construct a like compariso query when there are special characts
> > such as [ and ] in the string, i.e. [Y] to be searched? Thanks.
> > --
> > bic

8 Jun 2006 6:52 PM

SQL Menace

Another way is to double the brackets

select *
from
(
select 'abc[Y]def' as c1
union all
select 'abcYdef' as c1
) as t1
where c1 like '%[[Y]]%'
go

Denis the SQL Menace
http://sqlservercode.blogspot.com/

Alejandro Mesa wrote:
Show quote

> select *
> from
> (
> select 'abc[Y]def' as c1
> union all
> select 'abcdef' as c1
> ) as t1
> where c1 like '%\[Y\]%' escape '\'
> go
>
>
> AMB
>
> "Alejandro Mesa" wrote:
>
> > bic,
> >
> > use a scape character.
> >
> > Example:
> >
> > select *
> > from
> > (
> > select 'abc[Y]def' as c1
> > union all
> > select 'abc[Y]def' as c1
> > ) as t1
> > where c1 like '%\[Y\]%' escape '\'
> > go
> >
> >
> > AMB
> >
> > "bic" wrote:
> >
> > > How do I construct a like compariso query when there are special characts
> > > such as [ and ] in the string, i.e. [Y] to be searched? Thanks.
> > > --
> > > bic

8 Jun 2006 7:02 PM

Mike C#

Show quote

"SQL Menace" <denis.g***@gmail.com> wrote in message
news:1149792727.734355.86450@u72g2000cwu.googlegroups.com...
> Another way is to double the brackets
>
> select *
> from
> (
> select 'abc[Y]def' as c1
> union all
> select 'abcYdef' as c1
> ) as t1
> where c1 like '%[[Y]]%'
> go
>

Be careful with the bracket doubling thing - it can get confusing on complex
comparisons with lots of [ ]'s in them.

8 Jun 2006 6:43 PM

Mike C#

You can use the ESCAPE clause. In this example I'm using '!' as the escape
character:

DECLARE @x VARCHAR(100)
SELECT @x = '[Y]'
SELECT
CASE
WHEN @x LIKE '![Y!]' ESCAPE '!' THEN 'Match'
ELSE 'No Match' END AS Answer

Show quote

"bic" <b**@discussions.microsoft.com> wrote in message
news:B548D713-0022-47D1-A0E4-47630561159C@microsoft.com...
> How do I construct a like compariso query when there are special characts
> such as [ and ] in the string, i.e. [Y] to be searched? Thanks.
> --
> bic