normalize a list

Hi All,

Angellll gave me this cool function for normalizing a list a list from a string. Unfortunatly I need to modify it so that it accepts 2 delimiters and returns  the results in 2 columns. For example, with an initial column like:
aaa=1111; ababab=1234567; bbbb=8956;
ndydfy=7353; ndshy=376356

I need to return a result of:

aaa            1111
ababab      1234567
bbbb          8956
ndydfy       7353
ndshy        376356

Been working on it for a couple days, and its doing my head in. Can anyone help???

Thanks,

Pete

ate FUNCTION dbo.ParmsToList (@Parameters varchar(500), @delimiter varchar(10) )
returns @result TABLE (Value varchar(8000))
AS  
begin
    declare @dx varchar(9)
    --declare @loops int
    -- set @loops = 0
 
     DECLARE @TempList table
          (
          Value varchar(8000)
          )
 
     if @delimiter is null  set @delimiter = ' '
     if datalength(@delimiter) < 1 set @delimiter = ' '
     set @dx = left(@delimiter, datalength(@delimiter)-1)
 
     DECLARE @Value varchar(8000), @Pos int
 
     SET @Parameters = @Parameters + @delimiter
     SET @Pos = CHARINDEX(@delimiter, @Parameters, 1)
 
     IF REPLACE(@Parameters, @delimiter, @dx) <> ''
     BEGIN
          WHILE @Pos > 0 --and @Loops < 100
          BEGIN
              -- set @loops = @loops + 1
               SET @Value = LEFT(@Parameters, @Pos - 1)
               IF @Value <> ''
               BEGIN
                    INSERT INTO @TempList (Value) VALUES (@Value) --Use Appropriate conversion
               END
               SET @Parameters = SUBSTRING(@Parameters, @Pos+ datalength(@delimiter),500)
               SET @Pos = CHARINDEX(@delimiter, @Parameters, 1)
 
          END
     END    
     INSERT @result
     SELECT value
        FROM @TempList
     RETURN
END  
select t11.col1, f.value
 from yourtable t1
cross apply dbo.parmstolist(t1.col1, '; ') f

Select allOpen in new window