none
how to separate columns in sql server based on conditions RRS feed

  • Question


  • Hi I have one dobut in sql server .
    how to get firstname and lastname and productname infomarion in same column  in sql server 


    Table : empdetails 

    CREATE TABLE [dbo].[empdetails](
    [mailname] [varchar](100) NULL,
    [eid] [int] NULL

    INSERT [dbo].[empdetails] ([mailname], [eid]) VALUES (N'xyz_yy@sys.com', 1)
    GO
    INSERT [dbo].[empdetails] ([mailname], [eid]) VALUES (N'unike_te@oni.com', 2)
    GO
    INSERT [dbo].[empdetails] ([mailname], [eid]) VALUES (N'union_yy_ee@bit.com', 3)
    GO

    based on above data I want output like below 


    FirstName  | LastName | MiddleName | productid  |eid
    xyz        |yy        |null        | sys        |1
    unike      |te        | null       |oni         |2
    union      |yy        |ee          |bit         |3


    I tired like below 
     select substring (mailname ,1,charindex(mailname,'_',1)as firstname,substring(mailname,charindex(mailname,'_',len(mailname))last name
    from empdetails


    but above query is not given expeted result.
    can you please tell me how to write a query to achive this task in sql server 



    Sunday, August 11, 2019 12:55 PM

All replies

  • can you please tell me how to write a query to achive this task in sql server

    Here is a solution that uses the PARSENAME function:

    -- code #1
    ;with 
    sepP1P2 as (
    SELECT eid, mailname,
           P1= substring (mailname, 1, charindex ('@', mailname) -1),
           P2= substring (mailname, charindex ('@', mailname) +1, len (mailname))
      from empdetails 
    ),
    Padr as (
    SELECT *, 
           case when (len (P1) - len (replace (P1, '_', ''))) = 2
                     then replace (P1, '_', '.')
                     else replace (P1+'. ', '_', '.') end as P1_padr
      from sepP1P2
    )
    SELECT eid, mailname, 
           FirstName= parsename (P1_padr, 3),
           LastName= parsename (P1_padr, 2),
           MiddleName= parsename (P1_padr, 1),
           productid= parsename (P2, 2)
      from Padr;
     

    If this answer was helpful, remember to mark it.


    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    Sunday, August 11, 2019 2:26 PM
  • how to get firstname and lastname and productname infomarion in same column  in sql server 
    (...)
    can you please tell me how to write a query to achive this task in sql server

    Here's another solution that uses chained CTE and the substring and charindex functions:

    -- code #2
    with 
    sepP1P2 as (
    SELECT eid, mailname,
           P1= substring (mailname, 1, charindex ('@', mailname) -1),
           P2= substring (mailname, charindex ('@', mailname) +1, len (mailname))
      from empdetails 
    ),
    findS1 as (
    SELECT *, Pos1= 1
      from sepP1P2
    ),
    findS2 as (
    SELECT *, Pos2= charindex ('_', P1, Pos1) +1
      from findS1
    ),
    findS3 as (
    SELECT  *, Pos3= charindex ('_', P1, Pos2) +1
      from findS2
    ),
    tamS as (
    SELECT *, 
           Tam1= Pos2 - Pos1 -1,
           Tam2= case when Pos3 > 1 
                           then (Pos3 - Pos2 -1)
                      else (len(P1) - Pos2 +1) end,
           Tam3= case when Pos3 > 1 
                           then len(P1) - Pos3 +1
                      else 0 end
      from findS3
    )
    SELECT eid, 
           FirstName= substring (P1, Pos1, Tam1),
           LastName=  substring (P1, Pos2, Tam2),
           MiddleName= substring (P1, Pos3, Tam3),
           productid= substring (P2, 1, charindex ('.', P2) -1)
      from tamS 
      order by eid;

    ---

    Other solutions are described in the article “Separate multivalued text (string split)”.

     

    If this answer was helpful, remember to mark it.


    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    Sunday, August 11, 2019 2:32 PM
  • >> how to get firstname and lastname and product name information in same column  in sql server  <<

    You've never read a single book on RDBMS have you? The whole basis of First Normal Form (1NF)  is that all columns are scalar values. Have you ever heard of Dr. E. F. Codd? You still don't understand what a key is and you still don't know the syntax for insertion statements. Instead of posting on the forum, why don't you take some time and actually learn something about SQL and RDBMS?

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, August 11, 2019 3:07 PM
  • Hi jairama,

     

    Would you like this one ?

     
    select substring (mailname ,1,charindex('_',mailname)-1)as firstname,
    case when charindex('_',mailname,charindex('_',mailname)+1)=0 
    then substring (mailname,
    				charindex('_',mailname)+1,
    				charindex('@',mailname)-charindex('_',mailname)-1) 
    else substring (mailname,
    				charindex('_',mailname)+1,
    				charindex('_',mailname,charindex('_',mailname)+1)-charindex('_',mailname)-1) end LastName,
    case when charindex('_',mailname,charindex('_',mailname)+1)=0 
    then null
    else substring (mailname,
    				charindex('_',mailname,charindex('_',mailname)+1)+1,
    				charindex('@',mailname)-charindex('_',mailname,charindex('_',mailname)+1)-1) end MiddleName,
    substring (mailname,
    				charindex('@',mailname)+1,
    				charindex('.',mailname)-charindex('@',mailname)-1) productid  ,
    eid
    from empdetails
    /*
    firstname    LastName      MiddleName   productid     eid
    ------------ ------------- ------------ ------------- -----------
    xyz          yy            NULL         sys           1
    unike        te            NULL         oni           2
    union        yy            ee           bit           3
    */
    

    Hope it will help you.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, August 12, 2019 6:00 AM
  • Hi jairama,

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, August 14, 2019 9:33 AM