问题描述
你好,
我有像
一样的桌子 名称|技能区域|
________________________
阿杰| .net,wcf | hyd
ram | java,.net |爆炸
我想在存储过程中找到具有Java,点等候选技能的人
您能在我的应用程序前面引导或结束任何代码段吗?我正在使用文本框进行输入,如java,.net
Hello,
I have Table like
name | skills | area|
________________________
ajay | .net,wcf| hyd
ram | java,.net| bang
I want to find who have candidates skills like java,dot in stored procedure
can u guide or end any snippets in front my application i am using textbox for for giving input like java,.net
推荐答案
SELECT name, area FROM YourTable
WHERE skills LIKE N'%java%' OR skills LIKE N'%.net%'
ORDER BY name
可以在此处找到更多有关LIKE的信息.
More on LIKE can be found here.
/****** Object: UserDefinedFunction [dbo].[Split] Script Date: 2012.05.17. 12:25:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
insert into @temptable(Items) values(@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end
GO
我们可以用它找到您想要的东西:
We can use it to find what you want:
create procedure FindSkilledPeople
@skillstolook nvarchar(100)
as
begin
SELECT name,skills,area FROM SkilledPeople, (select items from dbo.Split(@skillstolook,',')) as look
where items in (select items from dbo.Split(SkilledPeople.skills,','))
group by name,skills,area
having count(*)=(select count(*) from dbo.Split(@skillstolook,','))
end
用英语:列出所有我正在寻找的具备所有技能的人
PS:如果您需要技能排名(例如A,B!= B,A),这还不够
In english: list all people who has all skills I am looking for
PS: this is not enough if you need ranking in skills (eg. A,B != B,A)
这篇关于如何使用存储过程拆分字符串以查找数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!