Split Function in SQL Server 2005

有鑒於事情應該正常分工的政策下(其實是覺得資料庫使用率偏低@@), 在有欄位需要以類似split的函式取出所要的部份需求, 撰寫一函式與相關的SQL語法如下:

1. SQL Server 2005->Object Explorer->Databases->New Query
2. Create split function

IF OBJECT_ID (N'dbo.split', N'IF') IS NOT NULL
DROP FUNCTION dbo.split
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 idx =" CHARINDEX(@Delimiter,@String)" slice =" LEFT(@String,@idx" slice =" @String">0)
INSERT INTO @temptable(Items) VALUES(@slice)

SET @String = RIGHT(@String,len(@String) - @idx)
IF LEN(@String) = 0 BREAK
END
RETURN

3. Using nested queries with split function

SELECT DISTINCT FIELD1_NAME FROM TABLE1_NAME WHERE FIELD2_NAME = (
SELECT TOP (1) items FROM
dbo.split(
(SELECT FIELD3_NAME
FROM TABLE2_NAME
WHERE (FIELD4_NAME = 'FIELD4_VALUE')), ':')
)

參考Split Function in Sql Server