CODEWITHSUNDEEP
sql-serverssisetlssis-2008derived-column
Jorge
Jorge

Reputation: 485

Separate data with substring and findstring

I have a problem I think it's something simple but I'm just getting started on this, I have a .txt file that contains

Kayle;Osvorn;35;4399900433

What would be these my columns: First name;Last name;Age;Phone

I need to separate them through the process of transformation of the derived column into ETL but for now only the first and last name I have been able to extract and the rest I do not know how to continue.

I have this for the first two columns

Name = SUBSTRING(CustomerData,1,FINDSTRING(CustomerData,";",1) - 1)
Last Name = SUBSTRING(CustomerData,FINDSTRING(CustomerData,";",1) + 1,LEN(CustomerData))
Age = ?
Phone = ?

Does anyone have any idea how the expression would go?

Upvotes: 3

Views: 764

Answers (4)

Hadi
Hadi

Reputation: 37313

If you need to do that using a transformation, why not using the TOKEN() function?

Name = TOKEN(CustomerData,";",1)
Last Name = TOKEN(CustomerData,";",2)
Age = TOKEN(CustomerData,";",3)
Phone = TOKEN(CustomerData,";",4)

Upvotes: 0

sacse
sacse

Reputation: 3744

There is one simple way by doing the same operation on the REVERSEd string:

[Name] = SUBSTRING(@CustomerData,1,FINDSTRING(@CustomerData,";",1) - 1)
[Last Name] = SUBSTRING(@CustomerData, FINDSTRING(@CustomerData, ";",1) + 1, 
                                        FINDSTRING(SUBSTRING(@CustomerData, FINDSTRING(@CustomerData, ";",1)+1, LEN(@CustomerData)),";",1)-1)
Age =  REVERSE(SUBSTRING(REVERSE(@CustomerData), FINDSTRING(REVERSE(@CustomerData),";",1)+1, 
                                            FINDSTRING(SUBSTRING(REVERSE(@CustomerData), FINDSTRING(";",REVERSE(@CustomerData),1) + 1, LEN(@CustomerData)),";",1)-1))
Phone = REVERSE(SUBSTRING(REVERSE(@CustomerData),1,FINDSTRING(REVERSE(@CustomerData),";",1) - 1))

Upvotes: 0

mkRabbani
mkRabbani

Reputation: 16908

Please use this below logic to achieve your requirement-

Demo Here

DECLARE @T VARCHAR(200) = 'Kayle;Osvorn;35;4399900433'

DECLARE @index_1 INT
DECLARE @index_2 INT
DECLARE @index_3 INT

DECLARE @name VARCHAR(100)
DECLARE @last_name VARCHAR(100)
DECLARE @age VARCHAR(100)
DECLARE @phone VARCHAR(100)



SELECT @index_1 = CHARINDEX(';',@T,0) + 1
SELECT @index_2 = CHARINDEX(';',@T,@index_1 + 1) + 1
SELECT @index_3 = CHARINDEX(';',@T,@index_2 + 1) + 1

SELECT  
    @name = SUBSTRING(@T,0,@index_1 - 1),
    @last_name = SUBSTRING(@T, @index_1 ,@index_2 - @index_1 - 1),
    @age = SUBSTRING(@T,@index_2, @index_3 - @index_2 - 1),
    @phone = SUBSTRING(@T,@index_3,LEN(@T))


SELECT @name,@last_name, @age,@phone

Upvotes: 1

billinkc
billinkc

Reputation: 61201

There's no need to use a Derived Column transformation in an SSIS package. Instead, in your Flat File Connection Manager, define your field separator as the semicolon ; instead of the default comma ','. Indicate that it should ... identify columns and now your single column of CustomerData goes away and you have nice delimited columns.

If you have column headers, it should pull that out. Otherwise, you will need to specify no header and then go into the advanced tab and give them friendly names.

Upvotes: 1

Related Questions