Help me ESCAPE ! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help me ESCAPE !

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2005-05-08 : 10:25:07

It's not what you think... it's something entirely different

LIKE supports wildcarding specifications such as:

LIKE '[abc]%' -- find strings beginning with a, b or c

I need to search for strings that contain the [ character and I'm having trouble. These strings all begin with [, contain a title of some sort, end with ] and have more text. I need something like

LIKE '[%]%'

but the left [ must not be treated as "beginning of specified range". Seems to me that

LIKE '[%]%' ESCAPE '['

should do it but it doesn't.

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2005-05-08 : 12:17:42

[code]
LIKE '\[%\]%' ESCAPE '\'
[/code]

SamC
White Water Yakist

3467 Posts

Posted - 2005-05-08 : 13:59:31

quote:
Originally posted by LarsG

LIKE '\[%\]%' ESCAPE '\'

Doesn't seem to work. I did find the following which works:

LIKE '[[]%]%'

but ESCAPE leaves me perplexed.

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2005-05-08 : 14:07:26

It works for me e.g.


create table samc(c1 char(12))
insert into samc values('[abc]def')
select * from samc where c1 like  '\[%\]%' escape '\'

which returns one row.

Is there some detail you are withholding or that I'm missing?

SamC
White Water Yakist

3467 Posts

Posted - 2005-05-08 : 15:19:13

quote:
Originally posted by LarsG

It works for me e.g.

create table samc(c1 char(12))
insert into samc values('[abc]def')
select * from samc where c1 like '\[%\]%' escape '\'

which returns one row.

Is there some detail you are withholding or that I'm missing?

I tried again and it works fine. I must have had a type-o. Sorry for this oversight.

My tests show the 2nd \ isn't needed (no big deal).

Sam

linksavage
Starting Member

2 Posts

Posted - 2007-01-02 : 17:18:06

I have a similar issue, but slightly different. I'm also very new to SQL.

I have a table with a varchar text column that has percentage symbol as the last character in most of the rows (400,000 rows in the table).

I need to remove the percentage symbols so that I can change the column to a numeric data type. I understand that the % sign is a wildcard and that I need to escape it. Can someone give me the entire sql command that will accomplish this?

Thanks!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-02 : 18:28:22

linksavage, you should start a new thread rather than hijacking an old thread.

And no this isn't escaping data in your case. It's just replacing the percent sign with nothing.

To update your data:

UPDATE YourTable
SET YourColumn = REPLACE(YourColumn, '%', '')

Tara Kizer

linksavage
Starting Member

2 Posts

Posted - 2007-01-02 : 18:38:28

Thanks Tara!!!

quote:
Originally posted by tkizer

linksavage, you should start a new thread rather than hijacking an old thread.

And no this isn't escaping data in your case. It's just replacing the percent sign with nothing.

To update your data:

UPDATE YourTable
SET YourColumn = REPLACE(YourColumn, '%', '')

Tara Kizer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources