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

Open in new window

LVL 2
carter-greenanAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you have to create the function once, and your query would go like this:
select t11.col1
     , f.value
     , left(f.value, charindex('=', f.value)-1)
     , substring(f.value, charindex('=', f.value)+1, 100)
 from yourtable t1
cross apply dbo.parmstolist(t1.col1, '; ') f

Open in new window

0
 
carter-greenanAuthor Commented:
Thanks Mate, thats brilliant!!! :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.