Parse String SSIS

Question

Parse String SSIS

John215

SSC Eights!

Points: 871
More actions
March 18, 2014 at 9:26 am

#305241

Hello, I want to parse a string in SSIS. My strings looks like this:
CAT@@@DOG@@@yyyy
MOUSE@@@bbb@@@aaa
r@@@s@@@g
They are separated by @@@ and I need code that is able to pull the 1st or 2nd or 3rd characters.
Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

mz1derful Ten Centuries Points: 1234 More actions · Answer 1

try looking at the regular expressions. Here is just one article on this: http://msdn.microsoft.com/en-us/magazine/cc163473.aspx

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 2

Here is a splitter TVF that returns each data value as a row in a table. It relies on some SQL XML magic.

/*

USAGE:

SELECT [Value] FROM [dbo].[split_delimited_string]

('1||2||3||4||5||6||7||8||9||10||11||12||13||14||15||16||17||18||19||20','||')

WHERE Value IN (1,2,3,4,5,6,7,8,9,20)

SELECT [Value] FROM [dbo].[split_delimited_string]

('1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20',';')

SELECT [Value] FROM [dbo].[split_delimited_string]

('1[][]2[][]3[][]4[][]5[][]6[][]7[][]8[][]9[][]10[][]11[][]12[][]13[][]14[][]15[][]16[][]17[][]18[][]19[][]20','[][]')

*/

CREATE FUNCTION [dbo].[split_delimited_string]

(

@STR NVARCHAR(MAX),

@sep NVARCHAR(MAX)

)

RETURNS @value TABLE (Value NVARCHAR(MAX))

AS

BEGIN

DECLARE @xml XML

SELECT @xml = CONVERT(XML,'<r>' + REPLACE(@str,@sep,'</r><r>') + '</r>')

INSERT INTO @value(Value)

SELECT t.value('.','NVARCHAR(MAX)')

FROM @xml.nodes('/r') AS x(t)

RETURN;

END

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 3

Recommend using Script Component in the Data flow. The split function is normally faster than anything in T-SQL and less complicated than most other options.

Phil Parkin SSC Guru Points: 246956 More actions · Answer 4

Or maybe let SSIS do all the work by defining @@@ as a column delimiter.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 5

Phil Parkin (3/24/2014)
Or maybe let SSIS do all the work by defining @@@ as a column delimiter.

+1 for simplicity 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 6

Phil Parkin (3/24/2014)
Or maybe let SSIS do all the work by defining @@@ as a column delimiter.

touché