A SQL Function which used to split string by char

原创 2007年09月25日 17:28:00
IF exists (SELECT * from dbo.sysobjects where id = object_id(N'[dbo].[fnSplitByDelim]'and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fnSplitByDelim]
GO

/****** Object:  UserDefinedFunction [dbo].[fnSplitByDelim]    Script Date: 05/25/2006 20:22:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fnSplitByDelim]
(
    
@SourceString VARCHAR(max),
    
@Delim CHAR(1)
)
RETURNS @Result TABLE(SplittedValue varchar(255), Ident INT IDENTITY not null)
BEGIN

-- ============================================================================================ --
--
                                      FinTool                                                    --
--
 ============================================================================================ --

/***********************************************************************************
** Desc:    Function used to return the values from string with seperator
**            
**            
**            
**
**
** Note:    
**                
**
** Called By:    Stored Procedures
** 
**
** Author:   v-svarad
** Date:     Oct 1, 2006
************************************************************************************
** Change History
************************************************************************************
** Date         Author     Description
** ----------   ---------  ----------------------------------------------------------
** 10-01-2006    v-svarad   Created

**********************************************************************************
*/


    
DECLARE @Wordstart int@WordEND int@DoubleDelim varchar(2)
    
    
SET @Wordstart = 1
    
SET @WordEND = 1
    
SET @DoubleDelim = REPLICATE(@Delim2)

    
-- Prepare the data
    WHILE CHARINDEX(@DoubleDelim@SourceString> 0
        
SET @SourceString = REPLACE(@SourceString@DoubleDelim@Delim)
    
-- Left-trim it
    IF left(@SourceString1= @Delim
    
BEGIN
        
IF @Delim = ' '
            
SET @SourceString = LTRIM(@SourceString)
        
ELSE
            
SET @SourceString = SUBSTRING(
                
@SourceString,
                
PATINDEX('%[^' + @Delim + ']%'@SourceString),
                
LEN(@SourceString- PATINDEX('%[^' + @Delim + ']%'@SourceString+ 1)
    
END
    
-- Right-pad it
    IF right(@SourceString1<> @Delim
        
SET @SourceString = @SourceString + @Delim

    
WHILE @Wordstart > 0
    
BEGIN
        
-- Extract the next word
        SET @WordEND = CHARINDEX(@Delim@SourceString@Wordstart)
        
IF @WordEND > @WordStart
        
BEGIN
            
IF LEN(LTRIM(RTRIM(SUBSTRING(@SourceString@Wordstart@WordEND - @Wordstart)))) > 0
                
INSERT into @Result SELECT SUBSTRING(@SourceString@Wordstart@WordEND - @Wordstart)
            
SET @Wordstart = @WordEND + 1
        
END
        
ELSE SET @Wordstart = 0 -- Terminate the loop
    END

RETURN 
END
GO

GRANT SELECT ON DBO.[fnSplitByDelim] TO PUBLIC
GO
 

相关文章推荐

表中真正使用的空间(How to Determine Real Space used by a Table (Below the High Water Mark)

How to Determine Real Space used by a Table (Below the High Water Mark) [ID 77635.1]       修改时间 01-...

If you had to start from scratch which wireless protocol would you pick? --by Robert Dickens

如果你不得不从头开始学习无线通信协议,你会选谁?
  • f117c
  • f117c
  • 2011年01月07日 18:32
  • 536

how to convert AVFrame to texture used by glTexImage2D?

转自: http://stackoverflow.com/questions/5548674/how-to-convert-avframe-to-texture-used-by-glteximage2...

Caused by: java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0

1、错误描述 [ERROR:]2015-05-05 16:35:50,664 [异常拦截] org.hibernate.exception.GenericJDBCException: error ...

SQL Used TO Retrieve country region from hybris

创建国家表 CREATE TABLE `country` ( `PK` bigint(20) NOT NULL DEFAULT '0', `activeflag` tinyint(1) DE...
  • choelea
  • choelea
  • 2017年06月07日 17:33
  • 136

(C#)重写分隔符分割字符串 - string.Split(char[] separator) (New)

修改了以前的同名文章里的方法并且增加了另一个方法实现string.Split(char[] separator) using System; using System.Collections.G...

PL/SQL Scripts Used in Migrating to ASM Storage

PL/SQL Scripts Used in Migrating to ASM Storage   The following PL/SQL scripts perform tasks which...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:A SQL Function which used to split string by char
举报原因:
原因补充:

(最多只允许输入30个字)