Clever Geek Handbook

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.

+3


source to share


6 answers


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.

+6


source


Use these terms



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


source


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.

+2


source 


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


source


Try it.

Skills = 'C' OR Skills Like '%,C%' OR Skills Like '%C,%'

in WHERE

state

0


source 


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


source






More articles:


All Articles