Famous SQL Interview Question | First Name , Middle Name and Last Name of a Customer

Поделиться
HTML-код
  • Опубликовано: 28 янв 2025

Комментарии • 115

  • @ankitbansal6
    @ankitbansal6  10 месяцев назад +10

    Join 100 days of SQL challenges where I have created hand picked SQL interview questions to sky rocket your SQL skills.
    100daysofsql.com/

  • @addhyasumitra90
    @addhyasumitra90 4 месяца назад

    My approach: bit compilcated but inspired by your method and with diff approach:
    with CTE as (
    select customer_name, len(customer_name) - len(REPLACE(customer_name,' ','')) as spaces, value,
    ROW_NUMBER() OVER(order by customer_name ) as rn
    from customers
    cross apply string_split(customer_name, ' ')),
    CTE2 as (
    select customer_name,
    case when spaces=0 then customer_name
    when ROW_NUMBER() over(partition by customer_name order by rn)=1 then value end as first_name
    ,case when spaces

  • @GiriPrasath.D
    @GiriPrasath.D 10 месяцев назад +1

    From this question, i learned, Char index, substring, left and right functions, you are SQL hero Ankit.
    and how to use the substring with len and charindex to extract the first , middle and last name.

  • @dfkgjdflkg
    @dfkgjdflkg 10 месяцев назад +1

    Not surprised endlessly impressive mastery that can only be envied. thanks

  • @architsrivastava6649
    @architsrivastava6649 10 месяцев назад +6

    My interview in 30 mins and was watching your Sql playlist ❤️

  • @vsagar4b38
    @vsagar4b38 10 месяцев назад

    Liked the Logic and Explanation, superb Enjoyed. Thanks Ankit

  • @Ak12345-g
    @Ak12345-g 10 месяцев назад +1

    Thanks for sharing good problems❤ worth watching

  • @pradeeppatil7168
    @pradeeppatil7168 9 месяцев назад

    Hi Ankit, i guess i have made it more simple, just check it.
    select *,
    case when len(empname)-len(replace(empname,' ',''))=1
    OR len(empname)-len(replace(empname,' ',''))=2
    then substring(empname,1,charindex(' ',empname)) ELSE EmpName end as F_Name,
    case when len(empname)-len(replace(empname,' ',''))=2 then substring(empname,charindex(' ',empname)+1,CHARINDEX(' ',empname,CHARINDEX(' ',empname)+1)-charindex(' ',empname)) end as M_Name,
    case when len(empname)-len(replace(empname,' ',''))=1
    OR len(empname)-len(replace(empname,' ',''))=2 then substring(empname,charindex(' ',empname,5)+1,len(empname)) end as L_Name
    from Employee

  • @ayushmi77al
    @ayushmi77al 10 месяцев назад +8

    In postgresql it's very easy, we can use split_part function.

    • @ankitbansal6
      @ankitbansal6  10 месяцев назад +5

      Right but thats not available in most of other databases.

    • @ayushmi77al
      @ayushmi77al 10 месяцев назад +2

      So true, your SQL interview question series so very helpful, thank you so much for that 🙏

    • @anudipray4492
      @anudipray4492 8 месяцев назад

      Oracle function much better

  • @jhonsen9842
    @jhonsen9842 10 месяцев назад +1

    Could you please post more Data Engineering SQL Questions . I think these questions are more alligned to Data Analyst which is cool also. Looking some super hard questions on Join and CTE,Subqueries.

  • @srinubathina7191
    @srinubathina7191 10 месяцев назад

    Thank you Bro again explanation is next level

  • @usharanikallubhavi7466
    @usharanikallubhavi7466 9 месяцев назад +2

    Hi Ankit. To extract middle_name, can we write SUBSTRING(customer_name, first_space_position+1, second_space_position-1)

  • @AbhaKumari-oc1fj
    @AbhaKumari-oc1fj 3 месяца назад

    My solution:
    select name,
    split_part(name, ' ', 1) as first_name,
    split_part(name, ' ', 2) as scn_name,
    split_part(name, ' ', 3) third_name
    from names
    this working perfectly, please give suggestion or advice if any point i missed.

  • @neerajpathak7585
    @neerajpathak7585 10 месяцев назад +11

    This also seems to work fine for me :
    SELECT split_part(customer_name,' ',1) as first_name
    ,case when split_part(customer_name,' ',3) ='' then '' else split_part(customer_name,' ',2) end as second_name
    ,case when split_part(customer_name,' ',3) ='' then split_part(customer_name,' ',2) else split_part(customer_name,' ',3) end as third_name
    from
    customers

    • @Riteshkumar-r5o
      @Riteshkumar-r5o 6 месяцев назад

      Simple and easy solution for PostgreSQL thanks buddy for your effort

    • @neerajpathak7585
      @neerajpathak7585 6 месяцев назад

      @@Riteshkumar-r5o 👍🏻

  • @shyamu431
    @shyamu431 10 месяцев назад

    Thanks Ankit, your questions and the way you solve it, is amazing. It's very good way to build the logical understanding by wathcing your videos. Coming to this particular video. If the name has more than 3 words i.e. Vijay Pratap Singh Rathore. The query will become more complex. Is there any other method to solve it.
    Once again, I appreciate your efforts in making these wonderful tutorials.

    • @ankitbansal6
      @ankitbansal6  10 месяцев назад +1

      Yes it will become complex. Also you need to decide what you want to keep in the middle name and last name. In postgres and redshift we have a split part function which can make the solution easy . I hope other databases introduce that function.

  • @deepeshmatkati3058
    @deepeshmatkati3058 10 месяцев назад

    Great explanation Ankit

  • @roshangangurde7188
    @roshangangurde7188 10 месяцев назад

    Great explanation sir 🙏🙏

  • @TanmayModi06
    @TanmayModi06 8 месяцев назад

    This Works fine:
    with cte as(
    select *, ROW_NUMBER() over(order by customer_name) as rn from customers
    ),
    cte1 as(
    select value,
    rn,
    ROW_NUMBER() over(partition by rn order by rn) as rk from cte
    cross apply string_split(customer_name, ' ')
    ),
    cte2 as(
    select *, case when rk = 1 then value end as firstname,
    case when rk 1 and rk (select Max(rk) from cte1 where rn = a.rn) then value end as middle_name,
    case when rk = (select Max(rk) from cte1 where rn = a.rn) and rk 1 then value end as lastname
    from cte1 a
    )
    select STRING_AGG(firstname,'') as firstname,
    STRING_AGG(middle_name,'') as middle_name,
    STRING_AGG(lastname,'') as lastname
    from cte2
    group by rn

  • @DE_Pranav
    @DE_Pranav 6 месяцев назад

    with cte as(
    select * from customers
    cross apply string_split(customer_name,' ')
    )
    ,cte2 as(
    select *,
    ROW_NUMBER() over(partition by customer_name order by(select null)) as rn,
    count(*) over (partition by customer_name) as cnt
    from cte
    )
    select customer_name,
    max(case when rn=1 then value end) as firstname,
    max(case when rn=2 and cnt=3 then value end) as middlename,
    max(case when (rn=2 and cnt=2) or rn=3 then value end) as lastname
    from cte2
    group by customer_name

  • @gauravkakhani7165
    @gauravkakhani7165 10 месяцев назад

    Using right function last name:
    Case when no_of_spaces= 0 then null when no_of_spaces= 1 then right(customer_name,len(customer_name) - firstspaceposition) else right(customer_name,len(customer_name)- second space position)end as lastname from cte;

  • @Ashu23200
    @Ashu23200 8 месяцев назад

    Superb!

  • @rahulkanojiya6256
    @rahulkanojiya6256 8 месяцев назад

    another way to solve the same question without using string fuctions :
    with cte as(
    select customer_name, value
    ,row_number() over (partition by customer_name order by customer_name) as rnk
    ,count(value) over(partition by customer_name order by customer_name) as cnt
    from customers
    cross apply string_split(customer_name , ' ')
    )
    ,cte2 as (
    select customer_name
    ,case when rnk = 1 then value end as first_name
    ,case when rnk = 2 and cnt = 3 then value end as middle_name
    ,case when (rnk = 2 and cnt = 2) or (rnk = 3 or cnt = 3) then value end as last_name
    from cte
    )
    select customer_name
    , max(first_name) as first_name
    , max(middle_name) as middle_name
    , max(last_name) as last_name
    from cte2 group by customer_name

  • @yakkaluruvijaysankar787
    @yakkaluruvijaysankar787 10 месяцев назад +1

    Your mic quality is not good. There is no clarity on what you are explaining. The information is very good and informative. Please do more videos like this.

  • @monuoriginal7425
    @monuoriginal7425 10 месяцев назад

    brother electoral bond par ek baar join laga ke bataona problem aarahi hai meko i am beginer also ....because data duplicates.....

  • @SwathiRavichandran-xh8wq
    @SwathiRavichandran-xh8wq 7 месяцев назад

    Hi Ankit ..your videos are good . Can you help how this can be achieved in oracle sql

  • @Riteshkumar-r5o
    @Riteshkumar-r5o 6 месяцев назад

    Hi Ankit, Good day!
    Can you please provide the solution for this question in PostgreSQL, as Position function in Postgre takes only two arguments,
    that's creating trouble getting the position of second space in the customer_name field. If not possible can you please just suggest me which function to use here for getting second space position in postgresql. Anyone from the community can suggest please.

  • @vikramjitsingh6769
    @vikramjitsingh6769 10 месяцев назад +1

    Those who are looking for MySQL version Solution - select *, substring_index(customer_name, ' ',1) as First , case when x >= 1 then substring_index(customer_name, ' ',-1) else null end as last ,
    case when x >= 2 then substring_index(substring_index(customer_name, ' ',2),' ',-(x-1)) else null end as middle
    from (select *, length(customer_name) - length(replace(customer_name,' ','')) as x from customers)x

  • @ss-hm6ey
    @ss-hm6ey 6 месяцев назад

    Hi Sir, i recently gave an interview at LTIMINDTREE and the very first question they asked was there are three tables and can we use left and right join on them. I didnt understand the question also clearly. Pls make a video on this and explain in detail. Thank you.

  • @mangeshbhumkar2075
    @mangeshbhumkar2075 10 месяцев назад

    In bigquery possible with split function with safe_offset(0),safe_offset(1) and safe_offset(2)

  • @007SAMRATROY
    @007SAMRATROY 10 месяцев назад

    So if there are N number of words in the name, we will have to derive N - 1 number of space positions right?

  • @naveenvjdandhrudu5141
    @naveenvjdandhrudu5141 10 месяцев назад +1

    Bro just post some easy interview questions in sql

  • @NextGen_Tech_Hindi
    @NextGen_Tech_Hindi 10 месяцев назад

    Love you 💓

  • @Soul-f3v
    @Soul-f3v 3 месяца назад

    with cte as(
    select *,ROW_NUMBER() over(partition by customer_name order by (select null)) as name_part,COUNT(*) over (partition by customer_name) as total_parts from customer cross apply string_split(customer_name,' '))
    select customer_name,
    max(case when name_part =1 then value else null end) as first_name,
    max(case when name_part =2 and total_parts > 2 then value else null end) as middle_name,
    max(case when name_part=3 or (name_part=2 and total_parts =2) then value else null end) as last_name
    from
    cte group by customer_name;

  • @arvindgurjar3300
    @arvindgurjar3300 10 месяцев назад

    Thank you so much

  • @boogieman8827
    @boogieman8827 10 месяцев назад

    Is Syllabus for Data engineer and Data Analyst the same? How much are the similarities?

    • @Tech_world-bq3mw
      @Tech_world-bq3mw 10 месяцев назад +1

      its different

    • @boogieman8827
      @boogieman8827 10 месяцев назад

      @@Tech_world-bq3mw how much SQL and python is same in Data Engineering vs Data Analytics?

  • @SiiitiiFreelancing-jl3ty
    @SiiitiiFreelancing-jl3ty 9 месяцев назад

    in Postgres? with strpos or position? how is second space found?

  • @NehaAgarwal-l8l
    @NehaAgarwal-l8l 9 месяцев назад

    with CTE as
    (
    select * from customers
    cross apply string_split(customer_name,' ')
    ),
    CTE2 as
    (select *,count(*) over(partition by customer_name) as words_count,row_number() over(partition by customer_name order by (select null)) as rn from CTE)
    select customer_name,max(case when words_count in (1,2,3) and rn=1 then value end) as first_name
    ,
    max(case when words_count in (3) and rn=2 then value end) as middle_name
    , max(case when words_count in (2) and rn=2 or words_count in (3) and rn=3 then value end) as last_name
    from CTE2
    group by customer_name

  • @adharshsunny5154
    @adharshsunny5154 10 месяцев назад +1

    Please do create AWS videos

  • @myselfrithish
    @myselfrithish 10 месяцев назад

    What is meant by SQL and t SQL is it necessary for data analytics job

  • @boogieman8827
    @boogieman8827 10 месяцев назад

    how much SQL and python is same in Data Engineering vs Data Analytics?

  • @PraveenSinghRathore-df3td
    @PraveenSinghRathore-df3td 3 месяца назад

    Easiest MYSQL solution:
    select substring_index(customer_name,' ',1) as first_name,
    case when (length(customer_name) - length(replace(customer_name,' ',''))) = 0 then null
    else substring_index(substring_index(customer_name,' ',2),' ',-1) end as middle_name,
    case when (length(customer_name) - length(replace(customer_name,' ',''))) = 0 then null
    else substring_index(customer_name,' ',-1) end as last_name from customers;

  • @chahatjain3745
    @chahatjain3745 Месяц назад

    With cte as (Select *,COUNT(1) over(partition by name_1) as No_of_words, ROW_NUMBER() over(partition by Name_1 order by (Select null)) as Temp
    from names cross apply string_split(Name_1,' '))
    Select Name_1,
    min(case when Temp=1 then value else null end) as First_Name,
    min(case when Temp=2 and No_of_words=2 then null
    when Temp=2 and No_of_words>2 then value else null end) as Middle_Name,
    min(case when No_of_words=2 and Temp=2 then value when No_of_words=3 and Temp=3 then value else null end)
    as Last_Name
    from cte group by Name_1;

  • @rajatbhat2074
    @rajatbhat2074 10 месяцев назад +1

    Hi Ankit,
    Tried below solution in PostgreSQL, its working. Let me know your thoughts.
    with cte as (
    select customer_name,
    length(customer_name)- length(replace(customer_name,' ','')) as no_of_spaces
    from customers
    )
    select customer_name,
    case when no_of_spaces >=0 then split_part(customer_name, ' ', 1) end as first_name,
    case when no_of_spaces =1 then split_part(customer_name, ' ', no_of_spaces+1) end as last_name
    from cte;

    • @ankitbansal6
      @ankitbansal6  10 месяцев назад

      It's good but the split part function is not available in most other databases

  • @akashmukherjee9011
    @akashmukherjee9011 8 месяцев назад

    with cte as (
    select customer_name,
    length(customer_name)-length(replace(customer_name,' ','')) as no_of_spaces
    from customers)
    select *,
    case
    when no_of_spaces = 0 then customer_name
    when no_of_spaces = 1 then substring_index(customer_name,' ',1)
    when no_of_spaces = 2 then substring_index(customer_name,' ',1)
    else null
    end as first_name,
    case
    when no_of_spaces = 2 then substring_index(substring_index(customer_name,' ',-2),' ',1)
    else null
    end as middle_name,
    case
    when no_of_spaces = 1 then substring_index(customer_name,' ',-1)
    when no_of_spaces = 2 then substring_index(customer_name,' ',-1)
    else null
    end as last_name
    from cte

  • @ishika7585
    @ishika7585 5 месяцев назад

    with cte as (
    select customer_name,
    value AS part,
    ROW_NUMBER() over (partition by customer_name order by customer_name) AS part_number
    from customers
    cross apply STRING_SPLIT(customer_name, ' ')
    )
    ,final as (
    select customer_name,
    max(case when part_number = 1 then part end) as first_name,
    max(case when part_number = 2 then part end) as middle_name,
    max(case when part_number = 3 then part end) as last_name
    from cte
    group by customer_name
    )
    select * from final;

  • @DEwithDhairy
    @DEwithDhairy 8 месяцев назад +1

    PySpark Version of this problem :
    ruclips.net/video/Zr6UXftnqOU/видео.html

  • @vinil9212
    @vinil9212 7 месяцев назад

    can middle name be extracted with LEFT or RIGHT?

  • @prakritigupta3477
    @prakritigupta3477 9 месяцев назад

    This is the solution in PostgresSQL
    select split_part(customer_name,' ',1) as first_name, split_part(customer_name,' ',2) as middle_name,
    split_part(customer_name,' ',3) as last_name from customers;

    • @ankitbansal6
      @ankitbansal6  9 месяцев назад +1

      If there is no middle name then your query will give last name as null and middle name will be last name.

  • @akashsonone2838
    @akashsonone2838 10 месяцев назад

    Hello Ankit , I've attempted another approach. Please inform me if it's functioning correctly in every corner case as well.
    WITH CTC AS(
    SELECT *,
    LEN(CUSTOMER_NAME) - LEN(REPLACE(CUSTOMER_NAME,' ','')) AS NO_OF_SPACES,
    LEFT(CUSTOMER_NAME, CHARINDEX(' ',CUSTOMER_NAME)) AS FIRST_NAME,
    RIGHT(CUSTOMER_NAME, CHARINDEX(' ',REVERSE(CUSTOMER_NAME))) AS LAST_NAME
    FROM CUSTOMERS
    )
    SELECT CASE WHEN NO_OF_SPACES = 0 THEN CUSTOMER_NAME ELSE FIRST_NAME END AS FIRST_NAME,
    CASE WHEN NO_OF_SPACES = 2 THEN SUBSTRING(CUSTOMER_NAME,LEN(FIRST_NAME)+2, LEN(CUSTOMER_NAME)-LEN(FIRST_NAME)-LEN(LAST_NAME)) END AS MIDDLE_NAME,
    CASE WHEN LEN(LAST_NAME) = 0 THEN NULL ELSE LAST_NAME END AS LAST_NAME
    FROM CTC

  • @paritoshjoshi5623
    @paritoshjoshi5623 10 месяцев назад

    with temp as( select customer_name,
    length(customer_name)-length(replace(customer_name,' ','')) ct from customers)
    select substring_index(customer_name,' ',1) as first_name
    ,if(ct=2,substring_index(substring_index(customer_name,' ',-2),' ',1) ,null) as middle_name
    ,if(ct=1 or ct=2,substring_index(customer_name,' ',-1),null) as last_name
    from temp;

  • @apurvasaraf5828
    @apurvasaraf5828 10 месяцев назад

    with cte as (select *,LEN(customer_name)-len(REPLACE(customer_name,' ','')) as l
    ,CHARINDEX(' ',customer_name) as f,
    CHARINDEX(' ',customer_name,CHARINDEX(' ',customer_name)+1) as s from Customers)
    select *,case when l=0 then customer_name else substring(customer_name,1,f-1) end as firstn,
    case when l

  • @DIVYATRIPATHI-w9f
    @DIVYATRIPATHI-w9f 10 месяцев назад

    Hello Sir in my sql it is showing error. I have used INSTR Function

  • @sarathmaya6083
    @sarathmaya6083 9 месяцев назад

    @ankitbansal6 please review RIGHT function for last name
    case when no_of_space=0 then null
    --when no_of_space=1 then SUBSTRING(customer_name,first_space_position+1,first_space_position)
    --when no_of_space=2 then SUBSTRING(customer_name,second_space_position+1,second_space_position)
    when no_of_space=1 then RIGHT(customer_name,first_space_position)
    when no_of_space=2 then RIGHT(customer_name,second_space_position-first_space_position-no_of_space)
    end as last_name

  • @kedarwalavalkar6861
    @kedarwalavalkar6861 10 месяцев назад

    My solution :
    with cte as (
    select *,
    substring_index(customer_name,' ',1) as a
    ,substring_index(substring_index(customer_name,' ',2),' ',-1) as b
    ,substring_index(customer_name,' ',-1) as c
    ,round((length(customer_name) - length(replace(customer_name,' ','')))/length(' '),0) as leng
    from custs
    )
    select
    a as first_name
    ,case when leng = 2 then b else null end as middle_name
    ,case when leng = 1 then b
    when leng = 2 then c else null end as last_name
    from cte;

  • @chrishkumar1250
    @chrishkumar1250 7 месяцев назад

    MYSQL
    SELECT
    substring_index(customer_name," ",1) as first_name,
    if (length(customer_name) - length(replace(customer_name, ' ', '')) > 1 ,
    substring_index(substring_index(customer_name," ",2), " ",-1), Null) as second_name,
    if (length(customer_name) - length(replace(customer_name, ' ', '')) >= 2 ,
    substring_index(substring_index(customer_name," ",3), " ",-1), Null) as third_name
    from customers

  • @anirbanbiswas7624
    @anirbanbiswas7624 5 месяцев назад

    SOLUTION FOR MYSQL BY USING SPACE_COUNT ONLY
    with cte as(select *, length(customer_name)-length(replace(customer_name,' ','')) as space_count from customers1)
    select customer_name,
    case when space_count=0 or space_count=1 or space_count=2
    then substring_index(customer_name,' ',1) end as trial,
    case when space_count=2 then substring_index(substring_index(customer_name,' ',2),' ',-1) else null end as mid_name,
    case when space_count=2 or space_count=1 then substring_index(customer_name,' ',-1) else null end as last_name
    from cte

  • @sahilummat8555
    @sahilummat8555 7 месяцев назад

    ;with cte as (
    select *,
    LEN(customer_name)-
    len(REPLACE(customer_name,' ','')) as spaces,
    CHARINDEX(' ',customer_name) as space_position,
    CHARINDEX(' ',customer_name,CHARINDEX(' ',customer_name)+1) as space_position_2
    from customers)
    select *,
    case when spaces=0 then customer_name
    when spaces!=0 then left(customer_name,space_position-1) end as first_name
    ,
    case when spaces>1
    then SUBSTRING(customer_name,space_position+1,space_position_2-space_position) end as middle_name
    ,
    case when spaces=1
    then right(customer_name,len(customer_name)-space_position)
    when spaces>1
    then right(customer_name,len(customer_name)-space_position_2)
    end as last_name
    from cte

  • @priyanshushak7705
    @priyanshushak7705 8 месяцев назад

    *for mysql versions which does not have charindex and its equivalent or if there are multiple middle names* : use below approach
    with cte_spaces as (
    select * ,
    length(customer_name)- length(replace( customer_name, ' ','')) as no_of_spaces
    from customers
    )
    select * ,
    substring_index(customer_name, " ",1) as first_name,
    case when no_of_spaces > 1 then
    substring_index(substring_index(customer_name, " ", (-1 * no_of_spaces)), " ", no_of_spaces -1)
    end as middle_name,
    case when no_of_spaces > 0 then
    substring_index(customer_name, " ",-1) end as last_name
    from cte_spaces

  • @naveenvjdandhrudu5141
    @naveenvjdandhrudu5141 10 месяцев назад

    There is only medium & complex in your play list

  • @skkholiya
    @skkholiya 2 месяца назад

    with cte as(
    select if(instr(customer_name," ")>0, substring(customer_name,1,
    instr(customer_name," ")),customer_name) first_name,
    trim(substr(customer_name,instr(customer_name," "),
    length(customer_name))) remaining_name
    from customers
    )
    select first_name,
    if(instr(remaining_name," ")>0,
    substring(remaining_name,1,instr(remaining_name," ")),"") middle_name,
    if(instr(remaining_name," ")>0,
    substring(remaining_name,instr(remaining_name," "),
    length(remaining_name)),remaining_name) last_name from cte;

  • @zainaltaf4935
    @zainaltaf4935 10 месяцев назад

    Just confused from where u find such questions 😅

    • @saipranay1318
      @saipranay1318 10 месяцев назад

      I feel the same !! lol 😀

  • @Hope-xb5jv
    @Hope-xb5jv 10 месяцев назад +1

    Logic fail if two or three space between name

    • @ankitbansal6
      @ankitbansal6  10 месяцев назад

      You can trim to single space first

  • @anudipray4492
    @anudipray4492 5 месяцев назад

    Anyone from oracle can do it

  • @hsk7715
    @hsk7715 10 месяцев назад

    it's look difficult

  • @Tech_world-bq3mw
    @Tech_world-bq3mw 10 месяцев назад

    You logic will fail if there is space in starting or in ending of string.

    • @ankitbansal6
      @ankitbansal6  10 месяцев назад +1

      In that case you can just trim the customer name in first cte and then as it is it will work

  • @AmanRaj-p8w
    @AmanRaj-p8w 9 месяцев назад

    MySQL Solution: with cte as (
    select customer_name,
    (length(customer_name) - length(replace(customer_name, ' ', '')) + 1) as cnt_of_words from customers
    )
    SELECT
    CASE
    WHEN cnt_of_words = 1 THEN customer_name -- Only one word, consider it as the first name
    WHEN cnt_of_words = 2 THEN SUBSTRING_INDEX(customer_name, ' ', 1) -- Two words, consider the first word as first name
    WHEN cnt_of_words = 3 THEN SUBSTRING_INDEX(customer_name, ' ', 1) -- Three words, consider the first word as first name
    END AS first_name,
    CASE
    WHEN cnt_of_words = 3 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(customer_name, ' ', 2), ' ', -1) -- Three words, consider the second word as middle name
    END AS middle_name,
    CASE
    WHEN cnt_of_words >= 2 THEN SUBSTRING_INDEX(customer_name, ' ', -1) -- At least two words, consider the last word as last name
    END AS last_name
    FROM
    cte;

  • @rubyshorts281
    @rubyshorts281 5 месяцев назад

    for mysql users
    WITH cte AS (
    SELECT *,
    LENGTH(customer_name) - LENGTH(REPLACE(customer_name, ' ', '')) AS spaces
    FROM customers
    )
    SELECT
    CASE
    WHEN spaces = 2 THEN
    SUBSTRING_INDEX(
    SUBSTRING_INDEX(customer_name, ' ', -spaces),
    ' ',
    spaces - 1
    )
    ELSE
    NULL
    END AS middle,
    CASE
    WHEN spaces >= 1 THEN
    SUBSTRING_INDEX(customer_name, ' ', -1) -- Last name
    ELSE
    NULL
    END AS last
    FROM cte;

  • @snehalpattewar7864
    @snehalpattewar7864 10 месяцев назад

    SELECT
    SUBSTRING_INDEX(name, ' ', 1) AS first_name,
    CASE
    WHEN LENGTH(name) - LENGTH(REPLACE(name, ' ', '')) > 1
    THEN SUBSTRING_INDEX(SUBSTRING_INDEX(name, ' ', -2), ' ', 1)
    ELSE NULL
    END AS middle_name,
    SUBSTRING_INDEX(name, ' ', -1) AS last_name
    FROM
    your_table_name;

  • @samiphani2473
    @samiphani2473 8 месяцев назад

    SELECT
    customer_name,
    CASE
    WHEN size(split(customer_name, ' ')) = 2 THEN split(customer_name, ' ')[0]
    ELSE split(customer_name, ' ')[0]
    END AS First_Name,
    CASE
    WHEN size(split(customer_name, ' ')) = 2 THEN NULL
    ELSE split(customer_name, ' ')[1]
    END AS Middle_Name,
    CASE
    WHEN size(split(customer_name, ' ')) = 2 THEN split(customer_name, ' ')[1]
    ELSE split(customer_name, ' ')[2]
    END AS Last_Name
    FROM customers;

  • @iramansari3625
    @iramansari3625 10 месяцев назад

    what if we have more then 2 space then @ankitbansal6 ?

  • @nipunshetty9640
    @nipunshetty9640 9 месяцев назад

    Hii Ankit bansal from my Side small request,
    As told by you in UR LINKEDIN PROFILE, Supply chain Analytics,
    Could you make one Video of SUPPLY CHAIN ANALYTICS BY TAKING PROCUREMENT SUPPLY CHAIN DATA SET AND Do DATA ANALYSIS so it will help me and even every audience please My Request Sir @ankitbansal6

  • @krsnabnsl
    @krsnabnsl 5 месяцев назад

    With PySpark DataFrame API
    df = customers_df.withColumn("splitter", split(customers_df["customer_name"], " "))
    df1 = df.withColumn("len", size("splitter"))
    df2 = df1.withColumn("FirstName", col("splitter")[0])\
    .withColumn("MiddleName", when(col("len")>2, col("splitter")[1]))\
    .withColumn("LastName", when(col("len")==2, col("splitter")[1]).otherwise(col("splitter")[2]))\
    .drop("splitter", "len")
    df2.show()