where in and like using sqlserver

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Hi,

I have a problem in query.
for example

declare @skill varchar(100);
set @skill='.net,java,php';

my query is like this
select * from job where title like '%'+@skill+'%';

but I want to get records title column contains .net , java and php individually

I mean

select * from job where title like '%java%'

select * from job where title like '%.net%'

select * from job where title like '%php%'

I want get the records like this in a single query

skills are dynamically changed.

please help me.
Thank you.

Posted 2-Sep-14 1:44am

NagaRaju Pesarlanka

Updated 2-Sep-14 2:00am

v2

Add a Solution

4 solutions

Solution 1

select * from job where jobskill IN('.net','java','php')

Posted 2-Sep-14 1:56am

ClimerChinna

Comments

NagaRaju Pesarlanka 2-Sep-14 8:00am

select * from job where title IN('.net','java','php')
I want only like operation, because there is no records for title='.net', title='java' and tilte='php'.
Thanks for reply me,
please give me this way soluation

Solution 2

Have you ever heard of the AND operator? I thought this would have been obvious.

SELECT fieldList FROM job
WHERE jobskill LIKE '%java%' 
  AND jobskill LIKE '%.net' 
  AND jobskill LIKE '%php%'

Perhaps you need to include '%SQL%' in your own search?

Posted 2-Sep-14 1:58am

Dave Kreskowiak

Comments

NagaRaju Pesarlanka 2-Sep-14 8:04am

the skills are dynamically changed, may be it will more than 10 skills

Dave Kreskowiak 2-Sep-14 10:17am

So what? I think Richards solution at the top is the best, but is a bit more complicated to understand.

My solution is easily understood and easily built using string concatenation, though I despise the method.

Gihan Liyanage 2-Sep-14 8:05am

I think this will not display any result according to his table data...

Dave Kreskowiak 2-Sep-14 10:18am

Then change the AND to an OR.

Solution 3

Use OR operator

SQL

SELECT job FROM job
WHERE jobskill LIKE '%java%'
  OR jobskill LIKE '%.net'
  OR jobskill LIKE '%php%'

Posted 2-Sep-14 2:03am

Gihan Liyanage

Updated 2-Sep-14 2:06am

v2

Comments

NagaRaju Pesarlanka 2-Sep-14 8:06am

the skills are dynamically changed, may be it will more than 10 skills

Gihan Liyanage 2-Sep-14 8:10am

Do you have included any main tables with Skills ?

NagaRaju Pesarlanka 2-Sep-14 8:15am

no,
I am getting records like this
select * from job where title like '%.net%'
I want to pass more values in one by one like title like '%java%', title like '%php%' etc

Gihan Liyanage 2-Sep-14 8:19am

If skills are dynamically change then better you would have an other table with mail skills. or pragmatically submit skills to the query. If I am in better understand about your requirement, you should have a list of skills already defined or given from UI to the query, then you should provide the list one by one using a loop to the existing query.

NagaRaju Pesarlanka 2-Sep-14 8:30am

Thank you for reply me,
looping is ok, but how to add previous loop result to current loop.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2014-09-02T02:27:00

Use a split function to extract the individual skills from the string:
T-SQL: Most Practical Split Function[^]

To return records which match any of the skills, try something like:

SQL

SELECT
    *
FROM
    jobs
WHERE
    Exists
    (
        SELECT 1
        FROM dbo.fn_split(@skill, ',') As S
        WHERE job.title LIKE '%' + S.item + '%'
    )