[plug] Re: [OT] SQL column splitting

[Anne Busby]

The following SQL depends on functions - some DBMS will have similar 
functions in-built else you should be able to create them (as below)

SELECT     Column1, Column2, SplitStr(Column3, ',', 1) AS Expr1, 
SplitStr(Column3, ',', IndexOf(Column3, ',')) AS Expr2, 
IndexOf(Column3,',') as Expr3
FROM   Split
Column1	Column2	Expr1	Expr2	Expr3
1	A	x		2
2       B       i       	-1
3       C       xx		3
				

if you need to do a continuous feed you should be able to create a 
trigger to copy the data or a stored procedure for scheduled feeds.
----------------------------------------------------------------------

ALTER FUNCTION dbo.SplitStr
	(
		@Str varchar(50),
		@Delimiter varchar,
		@Start integer
	)
RETURNS varchar(20)
AS
BEGIN
DECLARE @cnt INTEGER
DECLARE @tmpStr varchar(20)

	IF (@Start < 1 ) RETURN ('')

	SET @Cnt = @Start
	SET @tmpStr = ''
	WHILE @cnt < LEN(@Str)
	BEGIN
		IF (substring(@Str, @Cnt, 1) = @Delimiter )
			RETURN (@tmpStr)
		ELSE
			SET @tmpStr = @tmpStr + substring(@Str, @Cnt, 1)
			
		SET @Cnt = @Cnt + 1
	END
	
	RETURN (@Str)
END

----------------------------------------------------------------------

CREATE FUNCTION dbo.IndexOf
	(
	@Str varchar(50),
	@Delimiter varchar
	)
RETURNS integer
AS
BEGIN
DECLARE @Cnt integer
	
	SET @Cnt = 1
	WHILE @cnt < LEN(@Str)
	BEGIN
		IF (substring(@Str, @Cnt, 1) = @Delimiter )
			RETURN (@Cnt)
			
		SET @Cnt = @Cnt + 1
	END
	
	RETURN -1
END

----------------------------------------------------------------------



James Devenish wrote:
> In message <20041115101826.GA11461 at mail.guild.uwa.edu.au>
> on Mon, Nov 15, 2004 at 06:18:26PM +0800, James Devenish wrote:
> 
>>I want like to split
>>('1', 'A', 'x,y, z'), ('2', 'B', 'i')
>>into
>>('1', 'A', 'x'), ('1', 'A', 'y'), ('1', 'A', ' z'), ('2', 'B', 'i')
>