0 Replies - 1587 Views - Last Post: 21 March 2008 - 03:32 PM

#1 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1659
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

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
--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



Is This A Good Question/Topic? 0
  • +

Page 1 of 1