问题描述
这是包含我的 skills
C,C++ P,H,D ASP,.net,C,C#,C++,R+ C++
我需要找到所有包含 C 的条目.所以我使用 Skills LIKE ('%'+@Skill+'%') 格式化了一个查询,当我只想得到结果时,这给了我所有条目,包括 C++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.
从上面的例子中搜索,我只能得到 C,C++ 和 ASP, .net, C, C#, C++, R+ 行.我不能得到 C++ - 结果集中的最后一行.
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.
我的要求是,在搜索 C 而不是 C++ 时,我只需要获取 C.如何格式化此类查询?
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.
推荐答案
可以根据这些条件进行过滤
You can filter based on these conditions
如果搜索技能是列中的第一个技能Skills LIKE @Skill +',%'
如果搜索技能位于中间Skills LIKE '%,'+ @Skill+',%'
如果搜索技能在最后Skills LIKE '%,' + @Skill
如果搜索技能是唯一的技能 Skills = @Skill
if search skill is the only skill Skills = @Skill
查询
SELECT ... WHERE Skills LIKE '%,'+ @Skill+',%' OR Skills LIKE @Skill +',%' OR Skills LIKE '%,' + @Skill OR Skills = @Skill
编辑
另一个较短的查询可以是
Another shorter query can be
SELECT ... WHERE ',' + Skills + ',' LIKE '%,'+ @Skill+',%'
注意::您可能会遇到此类设计和查询的性能问题.如果可能,请考虑创建一个技能表来保存用户的所有技能.请参阅 Zohar Peled 关于如何改进您的设计的回答
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