Solved

decompose string

Posted on 2009-05-10
3
282 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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query 34 82
Create table from select - oracle 6 27
Sql Join Problem 2 33
Common Records between Sub Queries 4 15
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

863 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now