Splitting a PATH - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Splitting a PATH

Author

Topic

Kristen
Test

22859 Posts

Posted - 2005-02-17 : 14:50:17

I've been splitting, for example, comma delimited stuff using a Tally table for some time.

It occurred to me that where we have a PATH column, for tree-type structures, I could get ALL the parent records if I split the PATH - so for a normal delimited split:

A,B,C splits to "A", "B" and "C"

whereas for a path split I would like to split

/X/Y/Z to "/X", "/X/Y" and "/X/Y/Z"

It struck me that it would simply require that the output stage did a substring from the first character position, rather than the "previous" position, but then I never understood how the Tally splitter worked.

Here's the code that I've got so far, plus some "edge condition" test data if anyone could sort it out for me I'd appreciate it.


--
PRINT 'Create function KK_FN_PathSplit'
GO
IF EXISTS (SELECT * FROM dbo.sysobjects 
   WHERE id = object_id(N'[dbo].[KK_FN_PathSplit]') 
   AND xtype IN (N'FN', N'IF', N'TF'))
	DROP FUNCTION [dbo].[KK_FN_PathSplit]
GO

CREATE FUNCTION dbo.KK_FN_PathSplit
(
	@strSource	varchar(8000), 
	@strDelimiter	varchar(10) = '/',
	-- What to return if an item is blank?  (e.g. '', NULL or '[BLANK]')
	@strBlank	varchar(8000) = '[DELETE]'
				-- Use '[DELETE]' to delete any NULL items
)
RETURNS @tblArray TABLE
(
	Item	int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	Value	varchar(8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
/* WITH ENCRYPTION */
AS
/*
 * KK_FN_PathSplit	Split a string into its Path components
 *			e.g. /L1/L2/L3 will be split into /L1, /L1/L2 and /L1/L2/L3
 *
 * SELECT * FROM dbo.KK_FN_PathSplit('/A/B/C', '/', NULL)
 *
 * Returns:
 *
 *	Resultset of Value/Value pairs
 *
 * HISTORY:
 *
 * 17-Feb-2005 KBM  Started
 */
BEGIN
DECLARE @intLen		int,		-- Length of the source string
	@intNewDelimiter int

	IF DATALENGTH(@strDelimiter) > 1
	BEGIN
		-- Cannot safely handle BLANK fields with multi-character delimiter
		-- Use single character delimiter instead
		-- Find lowest character NOT used in @strSource
		SELECT	@intNewDelimiter = MIN(mb_int_ID)
		FROM	dbo.kk_MB_INT_Integer
		WHERE	@strSource NOT LIKE '%' + CHAR(mb_int_ID) + '%'
		SELECT	@strSource = REPLACE(@strSource, 
						@strDelimiter, 
						CHAR(@intNewDelimiter)),
			@strDelimiter = CHAR(@intNewDelimiter)
	END

	SELECT	@intLen = DATALENGTH(@strSource)

	-- Surround string with delimiters to make processing easier
	SELECT	@strSource = @strDelimiter + @strSource + @strDelimiter

	-- Split string using Tally table and Delimiters
	INSERT @tblArray(Value)
	SELECT	
		[Value] = COALESCE(
				NullIf(
					SUBSTRING 
					(
						@strSource, 
						1, 
						S2.mb_int_ID-1
					),
					 ''), 
				-- Convert blank items to user-supplied value
				@strBlank)
	FROM 	dbo.kk_MB_INT_Integer AS S1, 
		dbo.kk_MB_INT_Integer AS S2 
	WHERE	    SUBSTRING (@strSource , S1.mb_int_ID, 1) = @strDelimiter
		AND SUBSTRING (@strSource , S2.mb_int_ID, 1) = @strDelimiter
		AND S1.mb_int_ID < S2.mb_int_ID
		AND S2.mb_int_ID <= DATALENGTH(@strSource) + 1
--		AND S1.mb_int_ID > 1	-- Do NOT match the added leading delimiter
	GROUP BY S2.mb_int_ID
	HAVING	COALESCE(
			NullIf(
				SUBSTRING 
				(
					@strSource, 
					1, 
					S2.mb_int_ID-1
				),
				 ''), 
			-- Need to make NULL fail to match here, rather than "do nothing"!
			@strBlank, 'XXX')
		    <> '[DELETE]'
	ORDER BY S2.mb_int_ID

	RETURN
/* Test Rig

SET NOCOUNT ON
DECLARE @strTest varchar(20)
SELECT @strTest = '/A/B/C'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = 'A/B/C'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = '/A /B /C '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = 'A /B /C '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = '/ A/ B/ C'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = ' A/ B/ C'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = ' A / B / C '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = '/B/C'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = ' / B/ C'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = '/B /C '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = '/A//C'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = 'A//C'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = '/A //C '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = 'A //C '  SELECT @strTest, Item, '>'+Value+'<'
	 FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = '/ A/ / C'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = ' A/ / C'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = '/A / /C '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = 'A / /C '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = '/A/B/'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = 'A/B/'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = '/A /B / '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = 'A /B / '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = '/ A/ B/ '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = ' A/ B/ '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = '/A//'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = 'A//'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = '/A / / '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = 'A / / '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = '/ A/ / '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = ' A/ / '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = '//'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = '/ / / '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = ' / / '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = '/ // '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = ' // '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = '////XXX'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = '////'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')

SELECT @strTest = '/Aa/Bb/Cc'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = 'Aa/Bb/Cc'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = '/Aa /Bb /Cc '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = 'Aa /Bb /Cc '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = '/ Aa/ Bb/ Cc'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = ' Aa/ Bb/ Cc'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = '/ Aa / Bb / Cc '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = ' Aa / Bb / Cc '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = '/Bb/Cc'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = '/ / Bb/ Cc'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = ' / Bb/ Cc'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = '/Bb /Cc '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = '/Aa//Cc'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = 'Aa//Cc'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = '/Aa //Cc '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = 'Aa //Cc '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = '/ Aa/ / Cc'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = ' Aa/ / Cc'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = '/Aa / /Cc '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = 'Aa / /Cc '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = '/Aa/Bb/'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = 'Aa/Bb/'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = '/Aa /Bb / '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = 'Aa /Bb / '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = '/ Aa/ Bb/ '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = ' Aa/ Bb/ '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = '/Aa//'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = 'Aa//'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = '/Aa / / '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = 'Aa / / '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = '/ Aa/ / '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = ' Aa/ / '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = '/ / / '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')
SELECT @strTest = ' / / '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '/', '[BLANK]')

SELECT @strTest = '__A__B__C'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '__', '[BLANK]')
SELECT @strTest = 'A__B__C'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '__', '[BLANK]')
SELECT @strTest = '__A __B __C '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '__', '[BLANK]')
SELECT @strTest = '__A __B __C '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '__', '[BLANK]')
SELECT @strTest = 'A __B __C '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '__', '[BLANK]')
SELECT @strTest = '__ A __ B __ C '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '__', '[BLANK]')
SELECT @strTest = ' A __ B __ C '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '__', '[BLANK]')
SELECT @strTest = '__B__C'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '__', '[BLANK]')
SELECT @strTest = '__ __ B__ C'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '__', '[BLANK]')
SELECT @strTest = ' __ B__ C'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '__', '[BLANK]')
SELECT @strTest = '__B __C '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '__', '[BLANK]')
SELECT @strTest = '__A____C'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '__', '[BLANK]')
SELECT @strTest = 'A____C'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '__', '[BLANK]')
SELECT @strTest = '__A ____C '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '__', '[BLANK]')
SELECT @strTest = 'A ____C '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '__', '[BLANK]')
SELECT @strTest = '__ A__ __ C'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '__', '[BLANK]')
SELECT @strTest = ' A__ __ C'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '__', '[BLANK]')
SELECT @strTest = '__A __ __C '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '__', '[BLANK]')
SELECT @strTest = 'A __ __C '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '__', '[BLANK]')
SELECT @strTest = '__A__B__'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '__', '[BLANK]')
SELECT @strTest = 'A__B__'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '__', '[BLANK]')
SELECT @strTest = '__A __B __ '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '__', '[BLANK]')
SELECT @strTest = 'A __B __ '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '__', '[BLANK]')
SELECT @strTest = '__ A__ B__ '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '__', '[BLANK]')
SELECT @strTest = ' A__ B__ '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '__', '[BLANK]')
SELECT @strTest = '__A____'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '__', '[BLANK]')
SELECT @strTest = '__A____'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '__', '[BLANK]')
SELECT @strTest = 'A____'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '__', '[BLANK]')
SELECT @strTest = '__A __ __ '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '__', '[BLANK]')
SELECT @strTest = 'A __ __ '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '__', '[BLANK]')
SELECT @strTest = '__ A__ __ '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '__', '[BLANK]')
SELECT @strTest = ' A__ __ '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '__', '[BLANK]')
SELECT @strTest = '____'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '__', '[BLANK]')
SELECT @strTest = '__ __ __ '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '__', '[BLANK]')
SELECT @strTest = ' __ __ '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '__', '[BLANK]')
SELECT @strTest = '__ ____ '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '__', '[BLANK]')
SELECT @strTest = ' ____ '  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '__', '[BLANK]')
SELECT @strTest = '________XXX'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '__', '[BLANK]')
SELECT @strTest = '________'  SELECT @strTest, Item, '>'+Value+'<' 
	FROM KK_FN_PathSplit(@strTest, '__', '[BLANK]')


-- ===== Create Tally Table ==== --
SET NOCOUNT OFF

CREATE TABLE dbo.kk_MB_INT_Integer
(
	mb_int_ID int NOT NULL,
	CONSTRAINT [PK_kk_MB_INT_Integer] PRIMARY KEY CLUSTERED
	(
		[mb_int_ID]
	) WITH FILLFACTOR = 100 ON [PRIMARY] 
)

-- sp_indexoption cannot be executed within a transaction block
EXEC sp_indexoption 'dbo.kk_MB_INT_Integer', 'disallowrowlocks', TRUE
EXEC sp_indexoption 'dbo.kk_MB_INT_Integer', 'disallowpagelocks', TRUE

SET NOCOUNT ON

TRUNCATE TABLE kk_MB_INT_Integer

DECLARE @I int 
SELECT	@I = 1
WHILE @I <= 8000	-- this value should be the upper limit on the string length
BEGIN
	INSERT	dbo.kk_MB_INT_Integer(mb_int_ID) VALUES (@I) 
	SELECT	@I = @I+1
END
SET NOCOUNT OFF
-- ===== Create Tally Table -- ** RUN TO HERE ** ==== --

*/
END
--================== KK_FN_PathSplit ==================--
GO
PRINT 'Create function KK_FN_PathSplit - DONE'
GO
--

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-17 : 16:24:27

Kristen - I wasn't really sure what you were trying to do with '[Delete]', but here is my shot at the function. I used your same tally table and the test values. I also wasn't sure what you wanted them to look like.

Let me know where I got it wrong


CREATE FUNCTION dbo.KK_FN_PathSplit
(
	@strSource	varchar(8000), 
	@strDelimiter	varchar(10) = '/',
	-- What to return if an item is blank?  (e.g. '', NULL or '[BLANK]')
	@strBlank	varchar(8000) = '[DELETE]'
				-- Use '[DELETE]' to delete any NULL items
)
RETURNS @tblArray TABLE
(
	Item	int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	Value	varchar(8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
/* WITH ENCRYPTION */
AS
/*
 * KK_FN_PathSplit	Split a string into its Path components
 *			e.g. /L1/L2/L3 will be split into /L1, /L1/L2 and /L1/L2/L3
 *
 * SELECT * FROM dbo.KK_FN_PathSplit('/A/B/C', '/', NULL)
 *
 * Returns:
 *
 *	Resultset of Value/Value pairs
 *
 * HISTORY:
 *
 * 17-Feb-2005 KBM  Started
 */
BEGIN
	Declare @strDelimiter_orig varchar(10)

	-- Give delimiter unique beginning and ending
	Select 
		@strDelimiter_orig = @strDelimiter,
		@strSource = Replace(@strSource,@strDelimiter,'ñ'+@strDelimiter+'õ'), 
		@strDelimiter = 'ñ'+@strDelimiter+'õ'

	-- Split string using Tally table and Delimiters
	INSERT @tblArray(Value)
	Select
		Value = case when right(value,len(isnull(@strDelimiter_orig,@strDelimiter))) = isnull(@strDelimiter_orig,@strDelimiter) then value+@strBlank else value end
	From
		(
		Select distinct 
				-- Remove the special characters
			value = Replace(
					-- Insert blank value
					Replace(
						-- meat of the tally split
						left(@strSource,isnull(nullif(charIndex(@strDelimiter,@strSource,mb_int_ID),0)-1,len(@strSource)))
					,'õñ','õ'+@strBlank+'ñ')
				,@strDelimiter,isnull(@strDelimiter_orig,@strDelimiter))
		From kk_MB_INT_Integer 
		Where len(@strSource) >= mb_int_ID 
		and left(@strSource,charIndex(@strDelimiter,@strSource,mb_int_ID))<>left(@strDelimiter,1)
		)  A

	RETURN
End

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-02-17 : 18:55:13

OK, What am I missing here:

declare @splitstring varchar(20)
set @splitstring = '/X/Y/Z' -- to "/X", "/X/Y" and "/X/Y/Z"'
set @splitstring = @splitstring + '/'

select 
	left(@splitstring ,n-1) as path
from 
	numbers 
where 
	substring(@splitstring,n,1) = '/'
	and len(left(@splitstring ,n-1)) > 1

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-17 : 22:10:27

how do you handle something like 'a//c/d'?
if the string is ideal, it is easy to split, but if its not ideal (or guaranteed) then it is a little more complicated.

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2005-02-17 : 22:25:50

Yea, I guess if I took a bit more time examining Kristens sample data I would have noted the variances...
Silly me :)

Kristen
Test

22859 Posts

Posted - 2005-02-18 : 05:34:56

Brilliant! Thanks 7thNight

"I wasn't really sure what you were trying to do with '[Delete]'"

The idea was that


SELECT * FROM KK_FN_PathSplit('/A//B/C', '/', '[DELETE]')

would convert /A//B/C to "/A", "/A/B", "/A/B/C" rather than "/A", "/A/", "/A//B", "/A//B/C" i.e. accidental doubling of delimiters could be ignored. I suppose they could just be stripped first, but I sort of had in mind that TRIMMING might be used, so that "/A/ /B/C" could be treated as "/A/B/C"

having said that, performance is, as ever, important so accomodating any old rubbish data should be less important that doing it quickly!

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-18 : 07:46:10

To overcome the special cases with badly formed strings.
What about having a (recursive) function that makes an "ideal" string from a badly formed one.

eg.
select dbo.fnMakeGoodPath('A///B/ /C/D')
--------------------
A/B/C/D

then you can simplify the dbo.KK_FN_PathSplit (it only has to handle ideal paths)

simpler ?

rockmoose

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-18 : 13:17:37

I believe you could slightly modify the function that I built to do both...

Its kindof like a challenge we had one time... i found it: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=39370

EDIT: So I guess I'm saying recursize would not be necessary

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-18 : 16:10:17

How could I miss that!, nice challenge, and well done sn.

did this recursive table valued function.
(haven't seen one yet, so I thought it had some cool factor to it, and worth posting...)

create function splitpath(@path varchar(8000)) returns @rt table(hiearchy varchar(32)) as
begin
	-- max 32 levels, and must begin with /, / is delimiter
	insert @rt select @path
	set @path = left(@path,len(@path)-charindex('/',reverse(@path)))
	if @path <> '' insert @rt select hiearchy from dbo.splitpath(@path)
	return
end
GO

select * from dbo.splitpath('/X/YY/ZZZ')

rockmoose

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-18 : 16:52:03

I didn't realize you could pass table variables like that... thats pretty cool!

I always enjoyed those puzzles... too bad we haven't had any in a while

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources