• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 321
  • Last Modified:

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

0
wolfmm
Asked:
wolfmm
1 Solution
 
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
 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now