Solved

decompose string

Posted on 2009-05-10
3
290 Views
Last Modified: 2013-12-07
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
Comment
Question by:wolfmm
3 Comments
 
LVL 41

Expert Comment

by:pcelba
ID: 24349000
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
 
LVL 44

Expert Comment

by:GRayL
ID: 24349107
Does Replace(myStr),",","','")  -   look like it could work?
0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 24349382
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question