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: Monday, October 13, 2014 1:05 PM
Points: 10, Visits: 56
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
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 22, 2014 8:55 AM
Points: 49, Visits: 209
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: Friday, September 26, 2014 7:16 AM
Points: 487, Visits: 1,231
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 @ 9:50 PM
Points: 2,211, Visits: 5,951
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 @ 9:31 AM
Points: 5,162, Visits: 12,010
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.
Post #1553892
Posted Monday, March 24, 2014 2:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:55 PM
Points: 13,253, Visits: 11,033
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 @ 9:50 PM
Points: 2,211, Visits: 5,951
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