LIKE condition

Question

Post reply

LIKE condition

sqlpro

Hall of Fame

Points: 3859

August 21, 2005 at 9:16 pm

#90780

Posted - 08/21/2005 : 21:58:37

Hi Friends

i've following query to search for a word in a table

select * from task where Descr LIKE '% myword %' OR Descr LIKE 'myword %'

it works nicely but it does not include record that contains carriage return after the word am searching for.

I mean it finds first 2 records but not last one

1) hi there myword is sql

2) myword is sql

3)hi there myword ---here carriage return

is sql

any idea how to include 3rd record also.Thanks

Cheers

Viewing 15 posts - 1 through 14 (of 14 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Select * from task where replace(Descr, char(13), '') like '% myword %'

This will force a scan but it should work (unless you have char(10) in the return too)

sqlpro Hall of Fame Points: 3859 More actions · Answer 2

thanks Remi.

That worked nicely

I thought there would be other way to avoid functions in query.

sqlpro Hall of Fame Points: 3859 More actions · Answer 3

BTW Remi

i just ran into other issue

if a coulmn data ends with word am searching for then abv query will not return that record.

i mean records like this

"sql is myword"

any ideas !

Jeff Moden SSC Guru Points: 1004425 More actions · Answer 4

Here's a way to avoid the table scan...

select * from task where Descr LIKE '% myword %' OR Descr LIKE 'myword %' OR Descr LIKE '% myword'+char(10)+'%'

If you don't mind the table scan (presence of an index will get it down to an index scan but not an index seek), this will find myword for all the desirable conditions (I think)...

SELECT * from task where ' '+Descr+' ' LIKE '% myword %'

...might not work on Tuesdays

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Well now it's time to look into full-text indexing. This is really not something best handled with the like operator.

SQLBill SSC Guru Points: 51440 More actions · Answer 6

Why not just do:

select * from task

where Descr LIKE '%myword%'

-SQLBill

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

First it's a scan, 2nd '%myword%' would find a hit for 'OneOfMyWords' where FTS will not find a hit for that word.

SQLBill SSC Guru Points: 51440 More actions · Answer 8

Remi,

I knew it was a scan, but the poster is already doing: LIKE '% myword %' and LIKE '% myword', which would use scans.

I also knew about the oneofmywords issue, but I don't know if that's an issue for the poster. Depends on what type of words they will be searching for.

I agree with you about the full-text stuff...but if the poster doesn't do that (for whatever reason), I believe my suggestion is better than doing each one separately.

-SQLBill

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

We'll see what he has to say... maybe even he doesn't really know what he needs at the moment.

sqlpro Hall of Fame Points: 3859 More actions · Answer 10

I think i know what i need .i cant use sqlbill suggestion as Remi pointed out it'd include words like "oneofwords".

I think i'll use Jeff's suggestion.

Thanks everyone for ur advise and time

sqlpro Hall of Fame Points: 3859 More actions · Answer 11

Jeff

quick question

>>SELECT * from task where ' '+Descr+' ' LIKE '% myword %'

abv works beautifully .but i cant figure out whats exactly its doing .can u explain it briefly plz for my understanding.

BTW I dont worry abt table scan here as i've other WHERE conditions which will cause a table seek.

Thanks

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

It simply wraps the description with spaces so that you get a hit.

Jeff Moden SSC Guru Points: 1004425 More actions · Answer 13

Remi is correct... it takes the original description and wraps it in single spaces. Basically, it allows this single search ('% myword %') to work on the following descriptions...

something myword something

myword something

something myword

myword

That's because ' '+Descr+' ' makes the above look like this, instead... (note, single quotes added to see the leading and trailing spaces...)

' something myword something '

' myword something '

' something myword '

' myword '

In all cases, myword is wrapped in spaces in the above.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqlpro Hall of Fame Points: 3859 More actions · Answer 14

sqlpro

Hall of Fame

Points: 3859

August 22, 2005 at 4:57 pm

#584115

Thanks Jeff

for detailed info.that makes sense