How To Split column In SQL

SET NOCOUNT ON

 

DECLARE @Table table (Col001 varchar(100) NOT NULL)

 

INSERT INTO @Table VALUES('AABY TAMARA J')

INSERT INTO @Table VALUES('AADNESEN MARJORIE')

INSERT INTO @Table VALUES('Paola Godoy Calcagno Jensen')

INSERT INTO @Table VALUES('AARANT MAUREEN R')

INSERT INTO @Table VALUES('AARON MICHELLEE')

INSERT INTO @Table VALUES('AARON PATRICIA J')

INSERT INTO @Table VALUES('ABBA DAVID W')

INSERT INTO @Table VALUES('ABBATE ANTHONY P')

 

SELECT LEFT(Col001,CHARINDEX(' ',Col001)) AS FName

,CASE

WHEN LEN(Col001)-(CHARINDEX(' ',Col001)+CHARINDEX(' ',REVERSE(Col001))) > 0 THEN

SUBSTRING(Col001,CHARINDEX(' ',Col001)+1,LEN(Col001)-(CHARINDEX(' ',Col001)+CHARINDEX(' ',REVERSE(Col001))))

ELSE

''

END AS MName

,RIGHT(Col001,CHARINDEX(' ',REVERSE(Col001))-1) AS LName

FROM @Table

 

 

 

 

CREATE TABLE Inventory (ItemDescription VARCHAR(99))

INSERT Inventory VALUES ('245 HELIUM, COMPRESSED 2.2 UN1046' )

INSERT Inventory VALUES ('24adada5 HELIsadasdadUM, sdsdsd 6.6 UN99' )

INSERT Inventory VALUES ('24adada5 HELIsadasdadUM, sdsdsd 446.6777 UN9988888' )

INSERT Inventory VALUES ('24adada5 HEdUM, sdsdsd 446.0 UN9988' )

 

 

SELECT RIGHT(ItemDescription,PATINDEX('% %',

REVERSE(ItemDescription))-1) AS COL1,

LTRIM(REVERSE(LEFT(REVERSE(LEFT(ItemDescription,(LEN(ItemDescription)-PATINDEX('% %', REVERSE(ItemDescription))))),

PATINDEX('% %',REVERSE(LEFT(ItemDescription,(LEN(ItemDescription)-PATINDEX('% %', REVERSE(ItemDescription)))))))))

AS COL2

FROM Inventory

 

Result:

 

 

 

CREATE TABLE Names
    (
        FirstName VARCHAR(20),
        LastName VARCHAR(20) NULL
    )

INSERT Names SELECT 'Anthony Smith', NULL
INSERT Names SELECT 'Peter Ward', NULL
INSERT Names SELECT 'John Brown', NULL
INSERT Names SELECT 'Prince', NULL
INSERT Names SELECT 'Mary Jane Smith', NULL


UPDATE    Names
SET    FirstName =
        CASE
            WHEN CHARINDEX(' ', FirstName) > 0 THEN SUBSTRING(FirstName, 1,
CHARINDEX(' ', FirstName) - 1)
            ELSE FirstName
        END,
    LastName =
        CASE WHEN CHARINDEX(' ', FirstName) > 0 THEN SUBSTRING(FirstName,
CHARINDEX(' ', FirstName) + 1, LEN(FirstName) - CHARINDEX(FirstName, ' '))
        END

SELECT * FROM Names


Returns:

FirstName            LastName
-------------------- --------------------
Anthony              Smith
Peter                Ward
John                 Brown
Prince               NULL
Mary                 Jane Smith

 

 

 

 

create table #t ( [Name] varchar(40))

 

insert into #t ([Name]) values ('Smith,John E')

insert into #t ([Name]) values ('Smith,Bill')

insert into #t ([Name])  values ('Smith,Adam F')

insert into #t ([Name])  values ('St,Smith deWaal III')

 

---go

 

 

select LastName, FirstName, MiddleName

from (

  select

    Name,

    substring(Name,1,Comma-1) LastName,

    substring(Name,Comma+1,Spce-Comma-1) FirstName,

    nullif(substring(Name,Spce+1,40),'') MiddleName

  from (

    select

      Name,

      charindex(',',Name) Comma,

      charindex(' ',Name+space(1),charindex(',',Name)) Spce

    from #t

  ) D

) SplitNames

 

drop table #t

 

Result:

 

 

 

 

 

CREATE FUNCTION [dbo].[udf_Split2]

    (

      @List VARCHAR(8000),

      @Delimitter VARCHAR(5),

      @SplitOn VARCHAR(5)

    )

RETURNS @RtnValue TABLE

    (

      ID INT IDENTITY(1, 1),

      Value1 VARCHAR(100),

      Value2 VARCHAR(100)

    )

AS BEGIN

    DECLARE @SubString AS VARCHAR(50)

    DECLARE @Index1 AS INT

    DECLARE @Index2 AS INT

    WHILE( CHARINDEX(@SplitOn, @List) > 0 )

        BEGIN

            SELECT  @Index1 = CHARINDEX(@Delimitter, @List)

            IF @Index1 = 0

                SET @Index1 = 200

            SELECT  @SubString = LTRIM(RTRIM(SUBSTRING(@List, 1, @Index1 - 1)))

            SELECT  @Index2 = CHARINDEX(@SplitOn, @SubString)

 

            INSERT  INTO @RtnValue ( Value1, Value2 )

                    SELECT  Value1 = LTRIM(RTRIM(SUBSTRING(@SubString, 1,

                                                           @Index2 - 1))),

                            Value2 = LTRIM(RTRIM(SUBSTRING(@SubString,

                                                           @Index2 + 1,

                                                           LEN(@SubString)

                                                           - @Index2)))

 

            SET @List = SUBSTRING(@List, @Index1 + LEN(@SplitOn), LEN(@List))

        END

    RETURN

   END

 

 

TEST:

SELECT * FROM dbo.udf_Split2('This is,aTest|of the, Split2 function','|',',')

 

Result:

This is     aTest

of the      Split2 function

 

 

函数

CREATE   FUNCTION   [ dbo ] . [ Fun_Split ]  
(
  @field   varchar ( 1000 ),
  @ContainValue   varchar ( 8000 )
)
RETURNS   varchar ( 4000 )
AS
BEGIN
  declare   @strRet   varchar ( 10 )
  declare   @strTemp   varchar ( 1000 )
  declare   @StrSeprate   varchar ( 10 )
  DECLARE   @i   int
  set   @StrSeprate = ' , '
  set   @strRet = ' 1 '
  SET   @ContainValue = rtrim ( ltrim ( @ContainValue ))
  SET   @i = charindex ( @StrSeprate , @ContainValue )

  WHILE  ( @i >= 1 )
  BEGIN
   set   @strTemp = ( left ( @ContainValue , @i - 1 ))
   if ( charindex ( ' , ' + @strTemp +   ' , ' , ' , '   +   @field   +   ' , ' ) = 0 )
   begin
     set   @strRet = ' 0 '
   end
   SET   @ContainValue = substring ( @ContainValue , @i + 1 , len ( @ContainValue ) - @i )
   if ( @strRet = ' 0 ' )
     begin
         SET   @i = 0
     end
   else
     begin
         SET   @i = charindex ( @StrSeprate , @ContainValue )
     end
  END

   if ( @strRet = ' 1 ' )
   begin
    if ( charindex ( ' , ' + @ContainValue + ' , ' , ' , ' + @field + ' , ' ) = 0 )
       begin
         set   @strRet = ' 0 '
       end
   end
  
   return   @strRet
  end

 以上函数有两个输入参数，@field 是需要判断的参数，@ContainValue是否被包含的值。

当 @field 中包含@ContainValue 的所有值时，返回1，否者返回0.

以下是实际中的使用：

tablename: test

id	name	value
1	A	1,2,3,4
2	B	2,3,4
3	C	3,4,5

 

SQL->select * from test where dbo.Fun_Split(value,'2,4')=1

查询结果：

id	name	value
1	A	1,2,3,4
2	B	2,3,4

 

以上是我最近工作中编写的一个函数，是为了解决一个特殊的查询的问题。其实，产生这样的问题是源于自己在设计表结构时不合理的缘故。

关于这样的问题，还有一个更简单的方法（这里不谈数据库结构的设计），就是在Visual studio IDE 中创建一个SqlServerProject ，编写一个如下的函数：

 

代码

[Microsoft.SqlServer.Server.SqlFunction]
     public   static   string  StrContain( string  field, string  value)
    {
         bool  isContain  =   true ;
         if  ( ! string .IsNullOrEmpty(value))
        {
             string [] values  =  value.Split( ' , ' );
             foreach  (var item  in  values)
            {
                 if  (( ' , '   +  field  +   " , " ).IndexOf( ' , '   +  item  +   ' , ' )  <   0 )
                {
                    isContain  =   false ;
                     break ;
                }
            }
        }
        return  isContain ? " 1 " : " 0 " ;
    }

 

 

启用sqlserver2005 的 Clr(我用的sqlserver2005 和 sqlserver2008).

 

代码

-- 启动clr
exec  sp_configure  ' show advanced options ' ,  ' 1 '
go
reconfigure
go
exec  sp_configure  ' clr enabled ' ,  ' 1 '
go
reconfigure
go
exec  sp_configure  ' show advanced options ' ,  ' 1 '
go

-- 关闭clr
exec  sp_configure  ' clr enabled ' ,  ' 0 '
go
reconfigure
go

 

select * from test where dbo.strContain(value,@input)=1;

查询的结果与上边的sql function一样的。

由于我考虑到客户服务器权限的问题，所以还是使用了 sql function的方式。 

 

 

 

CREATE FUNCTION dbo.Split

(

    @RowData nvarchar(2000),

    @SplitOn nvarchar(5)

) 

RETURNS @RtnValue table

(

    Id int identity(1,1),

    Data nvarchar(100)

)

AS 

BEGIN

    Declare @Cnt int

    Set @Cnt = 1

 

    While (Charindex(@SplitOn,@RowData)>0)

    Begin

       Insert Into @RtnValue (data)

       Select

           Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

 

       Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))

       Set @Cnt = @Cnt + 1

    End

   

    Insert Into @RtnValue (data)

    Select Data = ltrim(rtrim(@RowData))

 

    Return

END

 

Test:

SELECT  *

FROM    dbo.Split('This is,aTest,of the, Split2 function',  ',')

Result:

1     This is

2     aTest

3     of the

4     Split2 function