A SQL Function which used to split string by char
来源:互联网 发布:java基础作业题 编辑:程序博客网 时间:2024/05/17 07:08
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(@Delim, 2)
-- Prepare the data
WHILE CHARINDEX(@DoubleDelim, @SourceString) > 0
SET @SourceString = REPLACE(@SourceString, @DoubleDelim, @Delim)
-- Left-trim it
IF left(@SourceString, 1) = @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(@SourceString, 1) <> @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
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(@Delim, 2)
-- Prepare the data
WHILE CHARINDEX(@DoubleDelim, @SourceString) > 0
SET @SourceString = REPLACE(@SourceString, @DoubleDelim, @Delim)
-- Left-trim it
IF left(@SourceString, 1) = @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(@SourceString, 1) <> @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
- A SQL Function which used to split string by char
- A useful function which used to split data by ',' from one column
- Powershell - how to split string by a string
- JavaScript:to write a function which increments a string, to create a new string
- pointer to a function used in arithmetic
- 71.Which arithmetic operations can be performed on a column by using a SQL function that is built in
- a pointer to a bound function may only be used to call the function
- a pointer to a bound function may only be used to call the function
- use regexp() to split a string
- Java – How to split a string
- How to split a string in C++
- OPatch 报错:OPatch needs to modify files which are being used by some processes.
- MySQL Split String Function
- Which tool should be used to find out a "jmp esp"
- How to split a string to array in objective-c?
- How to find the file which contain a specified string
- SQL CONVERT FUNCTION CONVERT DATETIME TO STRING
- Can't update table 'tbl' in stored function/trigger because it is already used by statement which invoked this stored function/t
- VB中实现ASP中Server.URLEncode与Server.URLDecode
- 301永久重定向实现方式及302重定向
- 中秋何时快乐
- bcp
- 通俗易懂客户端与服务器端交互原理(HTTP数据请求与HTTP响应,包括Servlet部分)
- A SQL Function which used to split string by char
- 北漂中
- 销售十八宗借口及破解
- 心情不错
- vb数据类型总结
- [Web]超实用的javascript小技巧
- 如何在linux下安装jdk
- 菜鸟学Python(11):在Django中怎么下载任意类型的文件?
- C#调用JavaScript引擎