Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# decompose string

Posted on 2009-05-10
Medium Priority
317 Views
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).
``````
0
Question by:wolfmm
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 43

Expert Comment

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
``````
0

LVL 44

Expert Comment

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

LVL 41

Accepted Solution

Sharath earned 2000 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
``````
0

## Featured Post

Question has a verified solution.

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

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.