full name splitting based on the condition

Question

Post reply

full name splitting based on the condition

sathishkm

SSC Veteran

Points: 239
More actions
November 2, 2022 at 1:47 am

#4107555
Hi All,
I have the full name in the columns. Here i would like to split the names according to the space in the names in SQL Query.
1. if space count is 1 then the name should be split in first name and last name
2. if space count is 2 then the name should be split in first name, middle name and last name
3. if space count is 3 then the first two word name should be split in first name and last two name should be in last name
Regards
Sathish

November 7, 2022 at 7:43 am

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2157 More actions · Answer 1

Maybe something like this

drop table if exists #some_table;
go
create table #some_table (
  full_name_proper varchar(200) not null);

insert into #some_table values
('A'),
('A B'),
('A B C'),
('A B C D'),
('A B C D E');

select st.*, v.spaces, 
       s1_val.string1,
       isnull(s2_val.string2, '') string2,
       s3_val.string3
from #some_table st
     cross apply (values (len(concat(st.full_name_proper, '@'))-len(concat(replace(st.full_name_proper, ' ', ''), '@')))) v(spaces)
     cross apply (select string_agg(ss.value, ' ') within group (order by ss.ordinal) 
                  from string_split(st.full_name_proper, ' ', 1) ss
                  where ss.ordinal<=(v.spaces+1)/2) s1_val(string1)
     outer apply (select ss.value
                  from string_split(st.full_name_proper, ' ', 1) ss
                  where ss.ordinal=v.spaces
                        and ss.ordinal=2) s2_val(string2)
     cross apply (select string_agg(ss.value, ' ') within group (order by ss.ordinal) 
                  from string_split(st.full_name_proper, ' ', 1) ss
                  where ss.ordinal>v.spaces/2+1) s3_val(string3)
where v.spaces between 1 and 3;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

Reminder... String_Split does not have an ordinal position of elements in the return in 2019. You'll need to use DelimitedSplit8K or something similar.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

@Sathish,

Are you all set? If not, take a look at the article at the first link in my signature line below to learn how to post some "Readily Consumable Data" so I can test against your data and I'll give it a shot for you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)