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.

回答1:

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

回答2:

Use these conditions

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

回答3:

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.

回答4:

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,_%'

回答5:

Try this.

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

回答6:

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