-- Create sample utility table with numbers
CREATE TABLE Numbers (
n INT PRIMARY KEY)
INSERT INTO Numbers
SELECT number
FROM master..spt_values
WHERE type = 'P'
AND number BETWEEN 1 AND 100
GO
-- Extract function to perform the string split
CREATE FUNCTION dbo.Extract(
@string VARCHAR(200),
@delimiter CHAR(1),
@idx INT)
RETURNS VARCHAR(200)
AS
BEGIN
RETURN
CASE @idx
WHEN 0 THEN @string
ELSE
(SELECT string
FROM
(SELECT SUBSTRING(@string, n,
CHARINDEX( @delimiter, @string + @delimiter, n ) - n ),
n + 1 - LEN(REPLACE(LEFT(@string, n), @delimiter, ''))
FROM Numbers
WHERE SUBSTRING(@delimiter + @string, n, 1) = @delimiter
AND n < LEN(@string) + 1) AS T(string, idx)
WHERE idx = @idx)
END
END
GO
-- Test
DECLARE @foo VARCHAR(100)
SET @foo = 'Joe*Doe*123 Main Street'
SELECT dbo.Extract(@foo, '*', 2) -- returns 'Doe'
SELECT dbo.Extract(@foo, '*', 3) -- returns '123 Main Street'
SELECT dbo.Extract(@foo, '*', 0) -- returns entire string
SELECT dbo.Extract(@foo, '*', 9) -- returns null
using System.Collections;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public class CLRSplit
{
[Microsoft.SqlServer.Server.SqlFunction()]
public static string extract(string str, char delimiter, int index)
{
if (index == 0)
{ return str; }
try
{
string[] list = new string[100];
list = str.Split(new char[] { delimiter });
return list[index - 1];
}
catch
{
return null;
}
}
}
Labels: CLR, t-sql programming