如何使用SQL Server 2000在sp中提取逗号分隔的值 [英] how to extract comma seperated values in sp using SQL server 2000

问题描述

你好朋友，
我有像国家一样的表名.

Hello friends,
I have Table name as country like.

Id    CountryName
1     India
2     Aus
3     Pak
4     US
5     Eng

我的存储过程就像.

And my stored procedure is like.

CREATE PROCEDURE getCountry
@Id(VARCHAR 10)
AS
BEGIN
      IF @Id IS NOT NULL
         SELECT CountryName FROM Country where Id = @Id
      END
END
GO

我正在以字符串格式将参数作为Id = 1,2,3,4,5发送，

I am sending the parameter as Id = 1,2,3,4,5 in string format,
How can we extract the comma separated?

推荐答案

hi 只需在sql server中执行查询即可..这将为您提供有关逗号分隔的想法.

hi just execute the query in sql server..this will give you an idea about comma seperation.

declare  @roleIds varchar(250),
    @userId int
BEGIN
 set @roleIds=''1,2,3,4,5,6,7,''
DECLARE @sItem VARCHAR(8000)
While (Charindex('','',@roleIds)>0)
    Begin
        Select  @sItem = ltrim(rtrim(Substring(@roleIds,1,Charindex('','',@roleIds)-1)))
        Set @roleIds = Substring(@roleIds,Charindex('','',@roleIds)+1,len(@roleIds))
        declare @n int
        SET @n = CONVERT(int, @sItem)
        print @n
    End
END

希望对您有帮助....

I hope this may help you....

按以下步骤创建功能

CREATE A FUNCTION AS FOLLOWS

CREATE FUNCTION fn_Split_Up_Ids
(
   @Param_Ids varchar(500)
)
RETURNS @Id_Table TABLE(IDField int)

AS
BEGIN  
   IF (LEN(@Param_Ids) <= 0) 
      RETURN

   DECLARE @CommaPos smallint
   SET @CommaPos = CHARINDEX(',', RTRIM(LTRIM(@Param_Ids)))	  

   IF @CommaPos = 0
       INSERT INTO @Id_Table 
              VALUES(CONVERT(BIGINT ,RTRIM(LTRIM(@Param_Ids))))
   ELSE 
       BEGIN
           WHILE LEN(@Param_Ids) > 1
	   BEGIN
	     SET @CommaPos = CHARINDEX(',', RTRIM(LTRIM(@Param_Ids)))
             INSERT INTO @Id_Table 
                      VALUES(CONVERT(INT ,SUBSTRING(RTRIM(LTRIM(@Param_Ids)),1, @CommaPos - 1)))
	     SET @Param_Ids = SUBSTRING(RTRIM(LTRIM(@Param_Ids)), @CommaPos + 1 , LEN(RTRIM(LTRIM(@Param_Ids))))
	     SET @CommaPos = CHARINDEX(',', RTRIM(LTRIM(@Param_Ids)))
	     IF @CommaPos = 0
	     BEGIN
                 INSERT INTO @Id_Table VALUES(CONVERT(INT ,RTRIM(LTRIM(@Param_Ids))))
                 BREAK
	     END
	   END
       END
       RETURN 
  END

然后您需要如下修改SP:

AND THEN YOU NEED TO MODIFY YOUR SP AS BELOW:

CREATE PROCEDURE spGetCountry
@Id VARCHAR(100)
AS
BEGIN
         SELECT Country FROM tblCountry where Id IN (select * from fn_Split_Up_Ids(@Id))
END
GO

您在这里:)
现在拨打您的SP

HERE YOU GO :)
NOW CALL YOUR SP

EXEC spGetCountry '1,2'

在SQL查询中没有直接功能来拆分字符串.您需要自己做一个.下面是一个示例:

http://geekswithblogs.net/AngelEyes/archive/2007/04/12/111504.aspx [ ^ ]

There is no direct function to split a string in sql query. You need to make one of your own. Below is an example:

http://geekswithblogs.net/AngelEyes/archive/2007/04/12/111504.aspx[^]

这篇关于如何使用SQL Server 2000在sp中提取逗号分隔的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！