--User defined function CREATE FUNCTION dbo.SplitString(@str AS VARCHAR(8000)) RETURNS TABLE AS RETURN SELECT pos - LEN(REPLACE(LEFT(@str, pos ), ',', '')) + 1 AS Location, SUBSTRING(@str, pos , CHARINDEX(',', @str + ',', pos ) - pos ) AS Number FROM dbo.NumberList WHERE pos <= LEN(@str) AND SUBSTRING(',' + @str, pos , 1) = ',' GO --NumberList Table SET NOCOUNT ON; --Replace YourDB with the name of your database USE YourDB; GO IF OBJECT_ID('dbo.NumberList') IS NOT NULL DROP TABLE dbo.NumberList; GO CREATE TABLE dbo.NumberList(id INT NOT NULL PRIMARY KEY); DECLARE @maxNum AS INT DECLARE @i AS INT; SET @maxNum= 10000000; SET @i= 1; INSERT INTO dbo. NumberList VALUES(1); WHILE @i * 2 <= @maxNum BEGIN INSERT INTO dbo.NumberList SELECT id + @i FROM dbo.NumberList; SET @i = @i * 2; END INSERT INTO dbo.NumberList SELECT id + @i FROM dbo.NumberList WHERE id + @i <= @maxNum; GO
MSSQL User Defined Function to split a strin
Page 1 of 10 Replies - 1515 Views - Last Post: 21 March 2008 - 03:32 PM
#1
MSSQL User Defined Function to split a strin
Posted 21 March 2008 - 03:32 PM
Description: The first snippet requires you to have a table, named NumberList, which is populated with the numbers you'll need. That script is included in this snippet.
When you run the script for the NumberList table replace YourDB with the name of the database you're creating the table inThis is a snippet to create a User Defined Function to split a comma delimited string
Page 1 of 1