Query to find all matching substring strings
Here is the column containing my rows for skills
C,C++
P,H,D
ASP,.net,C,C#,C++,R+
C++
I need to find all records containing C
. So I formatted the request with Skills LIKE ('%'+@Skill+'%')
and this gives me all the entries including C++
when I just want to get the result only C
.
Searching from the above example, I should only get strings C,C++
and ASP, .net, C, C#, C++, R+
. I shouldn't be getting C++
- the last row in the result set.
My requirement is that I only need to retrieve C
when searching C
, not C++
. How should I format such a request?
I am using stored procedures to execute all queries.
You can filter based on these conditions
-
If the search skill is the first skill in the column
Skills LIKE @Skill +',%'
-
if the search skill is somewhere in the middle
Skills LIKE '%,'+ @Skill+',%'
-
if the search skill is at the end
Skills LIKE '%,' + @Skill
-
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 could be
SELECT ...
WHERE ',' + Skills + ',' LIKE '%,'+ @Skill+',%'
Note. ... You may run into performance issues with such a design and query. If possible, look at creating a skill sheet to hold all skills for the user. Please see Zohar Peled's answer on how to improve the design.
Use these terms
Skills LIKE '%C,%' OR Skills LIKE '%C' OR Skills = 'C'
As long as it is stored as a delimited string, you will have to use workarounds such as the answers you already received.
After a quick glance, most of them will answer your question, which means you can find a specific skill, however none of them can solve the problem, only a workaround. He likes to use a bandage to insert a hole into the boat.
What you should actually be doing is normalizing your database, which means that instead of storing the skills as a comma delimited string, you should create a skills table that has only one skill per record, and a personToSkill table that will contain a unique combination of personId and skillId. This is the correct way to transfer many, many relationships in a relational database. Of course, you will need a unique skill constraint as well as foreign keys between each link table.
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,_%'
Try it.
Skills = 'C' OR Skills Like '%,C%' OR Skills Like '%C,%'
in WHERE
state
set @skill = '%'+@skill+'%';
select * from table where skills like @skill