Using Like

Question

Using Like

sweath

SSC-Addicted

Points: 499
More actions
October 21, 2010 at 8:14 am

#240004

Hi All,
i have list of 80 words where i want to use like
ex my query is like this
select * from xzy where column like '%abc%' or column like '%asd%' or column like '%ad%' or column like '%ahd%' ............ 80 times
instead of writing 80 times is there any other way that i can do ??
Thanks in Advance

Viewing 7 posts - 1 through 6 (of 6 total)

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

Ray K SSC-Dedicated Points: 31164 More actions · Answer 1

Good question. I can't think of an answer.

Why do you need to do this? If you tell us what you're trying to achieve, maybe we can come up with an alternate solution.

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

sweath SSC-Addicted Points: 499 More actions · Answer 2

i have to select a column from a table which has all these 80 words so i am planning to write this using LIke.

please suggest me if you any alternate way

Thanks in Advance

Stejones SSC Veteran Points: 260 More actions · Answer 3

Maybe regular expressions?

here is a link to a codeplex project which may help get you started.

http://www.codeproject.com/KB/database/SqlRegularExpressions.aspx

It may be more work though....

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

Here's a sample of how you could do it:

-- Table to hold the words to search for

DECLARE @Patterns TABLE (

search_pattern varchar(50)

)

-- Table to hold the text to search in

DECLARE @test-2 TABLE (

test_text nvarchar(max)

)

-- Populate the test table with the text of the

-- stored procedures in database msdb

INSERT INTO @test-2

SELECT definition

FROM msdb.sys.sql_modules

-- Define some patterns to search for

INSERT INTO @Patterns VALUES ('sysmail')

INSERT INTO @Patterns VALUES ('jobserver')

INSERT INTO @Patterns VALUES ('schedule')

-- Select out the stored procedures that match any of the patterns

SELECT *

FROM @test-2 AS A

WHERE EXISTS (

SELECT 1

FROM @Patterns AS B

WHERE A.test_text LIKE '%' + B.search_pattern + '%'

)

Hope this helps

Gianluca

EDIT: changed INNER JOIN to EXISTS to avoid duplicate rows when more than one pattern is matched

-- Gianluca Sartori

sweath SSC-Addicted Points: 499 More actions · Answer 5

yep that worked Gianluca

Thanks everyone for your response

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 6

kancherla.ks (10/21/2010)
i have to select a column from a table which has all these 80 words so i am planning to write this using LIke.
please suggest me if you any alternate way

That doesn't answer the below question.

Ray K (10/21/2010)
Why do you need to do this? If you tell us what you're trying to achieve, maybe we can come up with an alternate solution.

We understand your list for your like statement, but what are you comparing it to? Why do you need to make this comparison? What type of application or report are you doing and what does the data you're search against look like?

What about the table and indexes you're executing these Like statements against? What's the structure?

Answer all these questions and we can give you a better answer to your original post.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.