Separate data with substring and findstring

Question

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?

This is a pain to handle in SQL Server, especially on 2008. You might want to handle outside of SQL Server. — Tim Biegeleisen, Jun 28 '20 at 04:20

score 1 · Answer 1 · answered Jun 28 '20 at 04:37

1

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.

answered Jun 28 '20 at 04:37

billinkc

59,250
9
102
159

I have to use the transformation, it is a requirement :(, if in the data entry you throw me that option for delimiters but I have to do it with transformation. – Jorge Jun 28 '20 at 04:39
1

So this is a homework assignment versus real-world requirements? – billinkc Jun 28 '20 at 16:16

score 1 · Answer 2 · answered Jun 28 '20 at 05:05

1

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

answered Jun 28 '20 at 05:05

mkRabbani

16,295
2
15
24

Thank you very much it worked out, I have an extra column of total savings, and I only have to show those that are greater than 0 as I would do for this column? I have it last so I use ```@tota_savings = SUBSTRING(@T,@index_3,LEN(@T)) ``` for the purpose of obtaining it – Jorge Jun 28 '20 at 05:58
Can you explain a bit more please? You can implement checking like- @tota_savings = SUBSTRING(@T,@index_3,LEN(@T)), but just make sure data type in both side is same. – mkRabbani Jun 28 '20 at 06:20
@Dylan the value returned from the SUBSTRING will be a STRING as well. – mkRabbani Jun 28 '20 at 06:30

sacse · Answer 3 · 2020-06-28T05:45:24.717

0

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))

edited Jun 28 '20 at 05:45

answered Jun 28 '20 at 04:41

sacse

3,634
2
15
24

With the reverse function in the age column, I get the phone as well – Jorge Jun 28 '20 at 05:02
@Dylan that is because I used your expression of [Last Name] which was wrong, I have updated the answer now. Please see if that works fine. – sacse Jun 28 '20 at 05:47

score 0 · Answer 4 · answered Jun 29 '20 at 20:34

0

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)

answered Jun 29 '20 at 20:34

Hadi

36,233
13
65
124

Separate data with substring and findstring

4 Answers4