decompose string

turn a string of form
'[/str1XYZ.html, /str2XYZ.html, /str3XYZ.html, ...]' into a a list of strings '/str1XYZ.html', '/str2XYZ.html', ...
Each occurence of XYZ represents a potential different 1-3 digit number, and 'strN's might be different in length, example of the string originally returned by query:
[/SomePage89.html, /subfolder/SomeOtherPage3.html, /newfolder/SomePage145.html, ...]


I started with substr functions, and increasingly more complex, nested locate functions, to move the starting point for a pattern to the right for each new element, but quickly gets unwieldy. Looking for a cleaner construct, like, pseudo-codish: 
find(find_me, look_here, occurence n), rather than find(find_me, look_here, start_at).

Open in new window

wolfmmAsked:
Who is Participating?
 
SharathData EngineerCommented:
create a function like the attached and call that function.

declare @var nvarchar(2000) = '[/SomePage89.html, /subfolder/SomeOtherPage3.html, /newfolder/SomePage145.html]'
select * from dbo.split(replace(replace(@var,'[',''),']',''),',')

/SomePage89.html
/subfolder/SomeOtherPage3.html
/newfolder/SomePage145.html
If you have data in a table, you can cross apply this function like this.

select *
from your_table
cross apply dbo.split(YourColumn,',')

CREATE FUNCTION [dbo].[split] 
       (@csv nvarchar(max), 
        @delim varchar(1))
        RETURNS @entries TABLE (entry nvarchar(100))
    AS BEGIN    
       DECLARE @commaindex int    
        SELECT @commaindex = CHARINDEX(@delim, @csv)    
            IF @commaindex > 0        
               BEGIN            
               INSERT INTO @entries -- insert left side            
               SELECT LTrim(RTrim(LEFT(@csv, @commaindex-1)))            -- pass right side recursively            
               UNION ALL            
               SELECT entry           
               FROM dbo.split(RIGHT(@csv, LEN(@csv) - @commaindex), @delim)           
               END    
          ELSE BEGIN            
               INSERT INTO @entries            
               SELECT LTrim(RTrim(@csv))       
               END    RETURN
           END
GO

Open in new window

0
 
pcelbaCommented:
Let suppose the string is comma separated and stored in @InpStr variable and its fragments are inserted into table OutStr:
DECLARE @TmpStr varchar(4000)
SET @TmpStr = LTRIM(RTRIM(@InpStr))
CREATE TABLE OutStr (strX varchar(1000))
 
-- Strip brackets
IF LEFT(@TmpStr, 1) = '['
  SET @TmpStr = LTRIM(RIGHT(@TmpStr, LENGTH(@TmpStr)-1))
IF RIGHT(@TmpStr, 1) = ']'
  SET @TmpStr = RTRIM(LEFT(@TmpStr, LENGTH(@TmpStr)-1))
 
WHILE LENGTH(@TmpStr) > 0
BEGIN
  IF CHARINDEX(',', @TmpStr) > 0
  BEGIN
    INSERT INTO OutStr VALUES (RTRIM(SUBSTRING(@TmpStr, 1, CHARINDEX(',', @TmpStr)-1)))
    SET @TmpStr = LTRIM(RIGHT(@TmpStr, LENGTH(@TmpStr)-CHARINDEX(',', @TmpStr)))
  END
  ELSE
  BEGIN
    INSERT INTO OutStr VALUES (RTRIM(@TmpStr))
    SET @TmpStr = ''
  END
END

Open in new window

0
 
GRayLCommented:
Does Replace(myStr),",","','")  -   look like it could work?
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.