Loop help

Transact-SQL
1

I need help on a loop function. I have 2 fields. A Id field and a text field. I need to seperate the text by the delimiter of "|".

When I use the loop discussed in this forum I would expect
SELECT * FROM [dbo].[tmp_fnParseList] (
'80891')

would get me

80891 84in WIDE
80891 LOW PROFILE BUCKET
80891 BOLT ON CUTTING EDGE

In the end I would like a function that separated the text of a field by the delimiter | and include in that the unit_id field so I can join it and use it in a report.

create table tmp_table (unit_id varchar(8), spike varchar(250))

insert into tmp_table
select '80891', '84in WIDE | LOW PROFILE BUCKET | BOLT ON CUTTING EDGE | '
union
Select '100085', '61HP KUBOTA | 12,100LBS OPERATING WEIGHT | 1.10CUYD CLASS | HYDRAULIC SSL QUICK COUPLER | Z-BAR LINKAGE | 3RD SPOOL HYDRAULIC VALVE | 17.5/65-20 TIRES | 2-LEVER CONTROLS | 8,700LBS STRAIGHT TIP LOAD'
union
select '78287', '30in WIDTH | DIGGING BUCKET | 11.9 CU ft / YD CAPACITY | 70SR/80CS MOUNTING | 50MM PINS | 182MM DIPPER WIDTH | SIDE CUTTERS | 5 HENSLEY XS10 TEETH | '

CREATE FUNCTION dbo.tmp_fnParseList(@UntId varchar(8))

RETURNS @Result TABLE (RowID SMALLINT IDENTITY(1, 1) PRIMARY KEY, Data VARCHAR(8000))
AS

BEGIN
DECLARE
@NextPos INT,
@LastPos INT,
@Text Varchar(250),
@Delimiter Char

SELECT	
	@NextPos = CHARINDEX(@Delimiter, @Text, 1),
	@LastPos = 0,
	@text=(Select spike Des from tmp_table where unit_id='80891'),
	@Delimiter='|'

WHILE @NextPos > 0
	BEGIN
		INSERT	@Result
			(
				Data
			)
		SELECT	SUBSTRING(@Text, @LastPos + 1, @NextPos - @LastPos - 1)

		SELECT	@LastPos = @NextPos,
			@NextPos = CHARINDEX(@Delimiter, @Text, @NextPos + 1)
	END

IF @NextPos <= @LastPos
	INSERT	@Result
		(
			Data
		)
	SELECT	SUBSTRING(@Text, @LastPos + 1, DATALENGTH(@Text) - @LastPos)

RETURN

END

2

No looping is required or desired.

CREATE TABLE tmp_table 
	( unit_id varchar(8)
	, spike varchar(250) ) ;
GO
CREATE FUNCTION dbo.tmp_fnParseList()
RETURNS TABLE 
RETURN
   WITH 
   n10 (n) AS
      ( SELECT n FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t(n) ),
   n100 (n) AS
      ( SELECT a.n FROM n10 a CROSS JOIN n10 b ),
   n10000 (n) AS
      ( SELECT a.n FROM n100 a CROSS JOIN n100 b ),
   n100000000 (n) AS
      ( SELECT a.n FROM n100 a CROSS JOIN n100 b ),
   Numbers(Number) AS
      ( SELECT TOP (8000) ROW_NUMBER() OVER ( ORDER BY (SELECT NULL) ) FROM n100000000 )  --SELECT * FROM Numbers
SELECT tt.unit_id
     , startPos = n.Number
     ,LTRIM(RTRIM(SUBSTRING(tt.spike, n.Number, CHARINDEX('|', tt.spike+'|', n.Number+1)-n.Number))) SplitValue
FROM Numbers n
CROSS APPLY tmp_table tt
WHERE Substring('|'+tt.spike, n.Number, 1) = '|'
AND n.Number BETWEEN 1 AND Len(tt.spike);
go
INSERT INTO tmp_table
VALUES ( '80891', '84in WIDE | LOW PROFILE BUCKET | BOLT ON CUTTING EDGE | ')
     , ( '100085', '61HP KUBOTA | 12,100LBS OPERATING WEIGHT | 1.10CUYD CLASS | HYDRAULIC SSL QUICK COUPLER | Z-BAR LINKAGE | 3RD SPOOL HYDRAULIC VALVE | 17.5/65-20 TIRES | 2-LEVER CONTROLS | 8,700LBS STRAIGHT TIP LOAD')
	 , ( '78287', '30in WIDTH | DIGGING BUCKET | 11.9 CU ft / YD CAPACITY | 70SR/80CS MOUNTING | 50MM PINS | 182MM DIPPER WIDTH | SIDE CUTTERS | 5 HENSLEY XS10 TEETH | ') ;

SELECT
   tfpl.unit_id
 , tfpl.startPos
 , tfpl.SplitValue
FROM dbo.tmp_fnParseList() tfpl
ORDER BY
   tfpl.unit_id
 , tfpl.startPos ;

GO
DROP TABLE tmp_table;
DROP FUNCTION dbo.tmp_fnParseList;
GO

image

1 Like
3

Thank you, I think. I have no idea what is going on here but I'm dissecting it in parts.

4

Thanks you Joseph. That worked. I'm not sure what is going on in the statement but it worked. :smile:

5

I'm glad it helped.