CODEWITHSUNDEEP
sqlsql-servert-sql
SoundStage
SoundStage

Reputation: 833

Query to find all matching rows of a substring

Here is the column containing my row entries for skills

C,C++
P,H,D
ASP,.net,C,C#,C++,R+
C++

I need to find all entries that contain C. So I formatted a query by using Skills LIKE ('%'+@Skill+'%') and this gives me all entries including C++ when I just want to get the result of C alone.

Searching from the above example, I must only get C,C++ and ASP, .net, C, C#, C++, R+ rows. I must not get C++ - last row in the resultset.

My requirement is that I need to get only C when searching for C and not C++. How do I format such a query?

I am using stored procedures to execute all the queries.

Upvotes: 3

Views: 912

Answers (6)

ughai
ughai

Reputation: 9890

You can filter based on these conditions

  1. if search skill is first skill in column Skills LIKE @Skill +',%'

  2. if search skill is any where in the middle Skills LIKE '%,'+ @Skill+',%'

  3. if search skill is at the end Skills LIKE '%,' + @Skill

  4. if search skill is the only skill Skills = @Skill

Query

SELECT ...
WHERE Skills LIKE '%,'+ @Skill+',%'
OR Skills LIKE @Skill +',%'
OR Skills LIKE '%,' + @Skill
OR Skills = @Skill

EDIT

Another shorter query can be

SELECT ...
WHERE ',' + Skills + ',' LIKE '%,'+ @Skill+',%'

Note:: You may face performance issues with such a design and query. If possible look into creating a skills table to hold all skills for a user. Please see Zohar Peled's answer on how to improve your design

Upvotes: 6

Zohar Peled
Zohar Peled

Reputation: 82524

As long as it is stored as a delimited string you will have to use workarounds like the answers you already got.
After a quick glance most of them will answer your question, meaning that you will be able to search for a specific skill, However none of them provides a solution to the problem, only a workaround. It's like using a bandaid to plug a hole in a boat.
What you actually should do is normalize your database, meaning that instead of keeping the skills as a comma delimited string, you should create a skills table, with only one skill per record, and a personToSkill table that will hold a unique combination of personId and skillId. This Is the correct way of handeling many to many relationships in a relational database. Of course, you will need a unique constraint on thd skill, as well as foreign keys between each relates table.

Upvotes: 2

Deepak
Deepak

Reputation: 51

set @skill = '%'+@skill+'%';
select * from table where skills like @skill

Upvotes: 0

sqluser
sqluser

Reputation: 5672

Use these conditions

Skills LIKE '%C,%' OR Skills LIKE '%C' OR Skills = 'C'

Upvotes: 2

Saravana Kumar
Saravana Kumar

Reputation: 3729

Try this.

Skills = 'C' OR Skills Like '%,C%' OR Skills Like '%C,%' in WHERE condition

Upvotes: 0

mohan111
mohan111

Reputation: 8865

declare @t table (name varchar(50))
insert into @t (name)values ('C,C++'),('P,H,D'),('ASP,.net,C,C#,C++,R+'),('C++')

select * from @t where name like '%C,_%'

Upvotes: 0

Related Questions