Splitting a string column on only first space in ADF

Question

Splitting a string column on only first space in ADF

Anonymous

I have a column let us say "FullName" which have below sample data and I want to split the data into 2 derived column fields "FirstName" and "LastName", So I need to split the FullName filed based on only first space we find and need to ignore rest spaces if we have in that field.

The desired output for fields "FirstName" and "LastName" should be as below.

FullName	FirstName	LastName
AAA	AAA	Null
AAA BBB	AAA	BBB
AAA BBB CCC	AAA	BBB CCC
AAA BBB CCC DDD	AAA	BBB CCC DDD

Looking forward to getting your help

Accepted answer

0 additional answers

Your answer

Answer 1

Pedro Fiadeiro 216

Hi,

Assuming you're using a data flow to make those transformations, you can use the following formulas with a derived column

FirstName : left(FullName, instr(FullName, ' '))
LastName: right(FullName, length(FullName) - instr(FullName, ' '))

Anonymous

2023-02-10T13:29:47.77+00:00

Hi @Pedro Fiadeiro Thank you so much for your reply, The above solution work when we have atleast 2 words in FullName, but when there is just one word in FullName, then that one word "AAA" here goes to "LastName" but it should remain in "FirstName" field.

attaching the snip shot for reference.
Pedro Fiadeiro 216 Reputation points

2023-02-10T13:56:24.7333333+00:00
Didn't consider those cases. You can amend it to use this instead:

FirstName - substringIndex(FullName,' ', 1)

LastName - iif(instr(FullName, ' ') == 0, '', right(FullName, length(FullName) - instr(FullName, ' ')))

I'm sure the LastName formula can possibly be amended to look better than that but that should work for what you need
Anonymous

2023-02-12T18:10:45.5566667+00:00

Thank you so much @Pedro Fiadeiro this solves my problem.

Share via

Splitting a string column on only first space in ADF

0 additional answers

Your answer