Parsing this String?
-
I have a table in a database. It has an ID field and a string field which has two delimiters ";" and ":".
The colon ":" joins the pair and the semi colon ";" breaks up the pairs. It is also possible to have X number of pairs in the row.
ID
STRING
1
Great:Not Great ; Wonderful:Not Wonderful
2
New: Old ; Broken:Fixed
I can use the SUBSTRING and CHARINDEX to get the first set (Great:Not Great), but not the last set (Wonderful:Not Wonderful).
ID
Split1
Split2
Split3
1
Great
Not Great
Wonderful:Not Wonderful
2
New
Old
Broken:Fixed
How would I parse this into separate rows to look like something below?
ID
Split1
Split2
Split3
Split4
1
Great
Not Great
Wonderful
Not Wonderful
2
New
Old
Broken
Fixed
OR
ID
Split1
Split2
1
Great
Not Great
1
Wonderful
Not Wonderful
2
New
Old
2
Broken
Fixed
Thank you!
-
You're pretty much there! By breaking the problem down to its lowest denominator, you could:
1. Loop through the 'string' attribute to extract each pair of text, place this into a working table. Perform this for all the records.
2. You can then perform the split to produce the results as per your second example.
-
Would you happen to have an example? that would help out a lot!
This is all I have
SELECT
statusID,
CHARINDEX(':',status)+1 AS dOH,
CASE
WHEN CHARINDEX(':',status)+1 = 1
THEN LTRIM(SUBSTRING(status,CHARINDEX(':',status)+1,50))
ELSE SUBSTRING(status,0,CHARINDEX(':',status))
END AS status1,
CASE
WHEN LEN(LTRIM(SUBSTRING(status,0,CHARINDEX(':',status)))) > 1
THEN LTRIM(SUBSTRING(status,CHARINDEX(':',status)+1,67))
ELSE NULL
END AS status2
FROM tblStatus
WHERE status IS NOT NULL
ORDER BY statusID
Thank you!
-
G'day all,
The following code assumes a particular pattern for the string. Specifically, a space before the ';' as our example shows. A bit of tweaking will make this a more generic solution. Hope this helps.
Wayne
DROP TABLE MyTable
GO
CREATE TABLE MyTable (
MyID INT,
MyString VARCHAR(2000)
)
GO
INSERT INTO MyTable (MyID, MyString)
SELECT 1, 'Great:Not Great ; Wonderful:Not Wonderful' UNION ALL
SELECT 2, 'New: Old ; Broken:Fixed' UNION ALL
SELECT 3, 'A: Able ; B:Baker ; C:Charlie ; D
og'GO
DROP TABLE MyTableSplit
GO
CREATE TABLE MyTableSplit (
MyID INT,
MyFirst VARCHAR(1000),
MySecond VARCHAR(1000)
)
GO
DECLARE @MinID INT,
@MaxID INT,
@WorkString VARCHAR(2000),
@Pair VARCHAR(2000),
@Left VARCHAR(1000),
@Right VARCHAR(1000)
SELECT @MinID = MIN(MyID),
@MaxID = MAX(MyID)
FROM MyTable
WHILE @MinID <= @MaxID
BEGIN
SELECT @WorkString = MyString + ' ;'
FROM MyTable
WHERE MyID = @MinID
PRINT 'Before split loop: Workstring = <' + @WorkString + '>'
WHILE CHARINDEX (':', @WorkString) > 0
BEGIN
PRINT 'Top of loop: Workstring = <' + @WorkString + '>'
SET @Pair = LEFT (@WorkString, CHARINDEX(';', @WorkString) - 1)
SET @Left = LTRIM(RTRIM(LEFT(@Pair,CHARINDEX(':',@Pair) - 1 )))
SET @Right = LTRIM(RTRIM(RIGHT(@Pair,LEN(@Pair) - CHARINDEX(':',@Pair) + 1)))
PRINT 'Before insert: Pair = <' + @Pair + '>, @Left = <' + @Left + '>, @Right = <' + @Right + '>'
INSERT INTO MyTableSplit (MyID, MyFirst, MySecond) VALUES (@MinID, @Left, @Right)
SET @WorkString = LTRIM(RTRIM(RIGHT(@WorkString, LEN(@WorkString) - LEN(@Pair) - 2)))
END
SET @MinID = @MinID + 1
END
SELECT * FROM MyTableSplit
-
Wayne,
You are awesome, thank you so much. This is exactly what I was looking for.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply