如何使用存储过程拆分字符串以查找数据 [英] How to split string find the data using stored procedure

问题描述

你好，
我有像
一样的桌子 名称|技能区域|
________________________
阿杰| .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

推荐答案

签出http://weblogs.sqlteam.com/dinakar/archive/2007/03/28/60150.aspx [ ^ ].

尝试以下查询:

Try with this query:

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.

第一个请参阅以下功能:

First see following function:

/****** 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)

这篇关于如何使用存储过程拆分字符串以查找数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！