Solved

decompose string

Posted on 2009-05-10
3
272 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
Comment Utility
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
Comment Utility
Does Replace(myStr),",","','")  -   look like it could work?
0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

772 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

11 Experts available now in Live!

Get 1:1 Help Now