Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Parse String SSIS Expand / Collapse
Author
Message
Posted Tuesday, March 18, 2014 9:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 6:42 AM
Points: 10, Visits: 58
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.
Post #1552254
Posted Thursday, March 20, 2014 9:35 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 8, 2014 7:10 AM
Points: 52, Visits: 222
try looking at the regular expressions. Here is just one article on this: http://msdn.microsoft.com/en-us/magazine/cc163473.aspx
Post #1553090
Posted Friday, March 21, 2014 7:47 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 7:13 AM
Points: 487, Visits: 1,246
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




Post #1553498
Posted Sunday, March 23, 2014 11:15 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:03 PM
Points: 2,532, Visits: 7,067
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.
Post #1553829
Posted Monday, March 24, 2014 1:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:39 PM
Points: 5,307, Visits: 12,334
Or maybe let SSIS do all the work by defining @@@ as a column delimiter.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1553892
Posted Monday, March 24, 2014 2:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:28 AM
Points: 13,630, Visits: 11,501
Phil Parkin (3/24/2014)
Or maybe let SSIS do all the work by defining @@@ as a column delimiter.


+1 for simplicity




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1553895
Posted Monday, March 24, 2014 2:21 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:03 PM
Points: 2,532, Visits: 7,067
Phil Parkin (3/24/2014)
Or maybe let SSIS do all the work by defining @@@ as a column delimiter.

touché
Post #1553896
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse