First name And last Name in SQL

  • Hello All,

    I have two column with First name and last Name.

    I want 3rd column With First name and Last name and I want 4th column with First letter of First name And Last Name.

    How Can I get This One.

    Example:

    First name Lastname FirstnameLastname FirstletteroffirstnameLastName

    John Hozack JohnHozack Jhozack

    Thanks

    Bhavesh

  • is this a test question?

    Its pretty basic T-SQL.

    Do you want this persisted as data or a computed column? dynamic as TSQL or a View?

  • BEGIN TRAN

    CREATE TABLE yourTable (

    [yourTable ID] INT IDENTITY NOT NULL,

    [First Name] VARCHAR(20) NOT NULL,

    [Last Name] VARCHAR(20) NOT NULL,

    [Full Name] AS [First Name] + ' ' + [Last Name],

    [Initial + Last Name] AS SUBSTRING([First Name],1,1) + ' ' + [Last Name])

    INSERT INTO yourTable

    SELECT 'John', 'Hozack'

    UNION ALL SELECT 'Todd', 'Toddingson'

    UNION ALL SELECT 'Jim', 'Jimson'

    SELECT * FROM yourTable

    ROLLBACK

    --Edit--

    Arrghh, I'm too slow again.


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • It's T-SQl

  • Thanks

  • But I have A names like

    John hozack MD

    CHRISTOPHERDODSON MD

    And I dont Want last Two Words means I do not want MD then ??/?

  • bhaveshp.dba (12/21/2011)


    But I have A names like

    John hozack MD

    CHRISTOPHERDODSON MD

    And I dont Want last Two Words means I do not want MD then ??/?

    Either a REPLACE like this: -

    BEGIN TRAN

    CREATE TABLE yourTable (

    [yourTable ID] INT IDENTITY NOT NULL,

    [First Name] VARCHAR(20) NOT NULL,

    [Last Name] VARCHAR(20) NOT NULL,

    [Full Name] AS [First Name] + ' ' + RTRIM(REPLACE([Last Name],'MD','')),

    [Initial + Last Name] AS SUBSTRING([First Name],1,1) + ' ' + RTRIM(REPLACE([Last Name],'MD','')))

    INSERT INTO yourTable

    SELECT 'John', 'Hozack MD'

    UNION ALL SELECT 'Todd', 'Toddingson'

    UNION ALL SELECT 'Jim', 'Jimson'

    SELECT * FROM yourTable

    ROLLBACK

    Or if you want to strip out anything after a space, then something like this: -

    BEGIN TRAN

    CREATE TABLE yourTable (

    [yourTable ID] INT IDENTITY NOT NULL,

    [First Name] VARCHAR(20) NOT NULL,

    [Last Name] VARCHAR(20) NOT NULL,

    [Full Name] AS [First Name] + ' ' + SUBSTRING([Last Name],1,CASE WHEN CHARINDEX(' ', [Last Name]) > 1

    THEN CHARINDEX(' ', [Last Name])-1

    ELSE LEN([Last Name]) END),

    [Initial + Last Name] AS SUBSTRING([First Name],1,1) + ' ' + SUBSTRING([Last Name],1,CASE WHEN CHARINDEX(' ', [Last Name]) > 1

    THEN CHARINDEX(' ', [Last Name])-1

    ELSE LEN([Last Name]) END))

    INSERT INTO yourTable

    SELECT 'John', 'Hozack MD'

    UNION ALL SELECT 'Todd', 'Toddingson'

    UNION ALL SELECT 'Jim', 'Jimson'

    SELECT * FROM yourTable

    ROLLBACK


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Very good script .i found which i was looking for.

  • WHY do you want to store derived data in the first place?

    What happens if the names change?

    This is a presentation issue that should be handled in the front end


    Brett

    😎

  • x002548 (12/21/2011)


    This is a presentation issue that should be handled in the front end

    Maybe not. What if there is no front end or it's simply a conversion required for output to a file where a front end wouldn't be involved? 😉

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

  • Cadavre (12/21/2011)


    Either a REPLACE like this: -

    Still think that might be a good idea? Try your code with the following names...

    BEGIN TRAN

    CREATE TABLE yourTable (

    [yourTable ID] INT IDENTITY NOT NULL,

    [First Name] VARCHAR(20) NOT NULL,

    [Last Name] VARCHAR(20) NOT NULL,

    [Full Name] AS [First Name] + ' ' + RTRIM(REPLACE([Last Name],'MD','')),

    [Initial + Last Name] AS SUBSTRING([First Name],1,1) + ' ' + RTRIM(REPLACE([Last Name],'MD','')))

    INSERT INTO yourTable

    SELECT 'John', 'Hozack MD'

    UNION ALL SELECT 'Todd', 'Toddingson'

    UNION ALL SELECT 'Jim', 'Jimson'

    UNION ALL SELECT 'Agora', 'Mdori'

    UNION ALL SELECT 'James', 'Hamdor'

    SELECT * FROM yourTable

    ROLLBACK

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

  • Cadavre (12/21/2011)


    Or if you want to strip out anything after a space, then something like this: -

    You forgot multi-word last names...

    BEGIN TRAN

    CREATE TABLE yourTable (

    [yourTable ID] INT IDENTITY NOT NULL,

    [First Name] VARCHAR(20) NOT NULL,

    [Last Name] VARCHAR(20) NOT NULL,

    [Full Name] AS [First Name] + ' ' + SUBSTRING([Last Name],1,CASE WHEN CHARINDEX(' ', [Last Name]) > 1

    THEN CHARINDEX(' ', [Last Name])-1

    ELSE LEN([Last Name]) END),

    [Initial + Last Name] AS SUBSTRING([First Name],1,1) + ' ' + SUBSTRING([Last Name],1,CASE WHEN CHARINDEX(' ', [Last Name]) > 1

    THEN CHARINDEX(' ', [Last Name])-1

    ELSE LEN([Last Name]) END))

    INSERT INTO yourTable

    SELECT 'John', 'Hozack MD'

    UNION ALL SELECT 'Todd', 'Toddingson'

    UNION ALL SELECT 'Jim', 'Jimson'

    UNION ALL SELECT 'Wilhelm', 'van de Graff MD'

    SELECT * FROM yourTable

    ROLLBACK

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

  • Borrowing shamelessly from Cadavre, the following includes the original test data he built as well as the 3 outlying cases I provided as well as an answer on the current requirements. (more on THAT in a minute)

    --===== Conditionally drop the test table to make reruns easier in SSMS

    IF OBJECT_ID('tempdb..#YourTable','U') IS NOT NULL

    DROP TABLE #YourTable

    ;

    --===== Create the test table

    CREATE TABLE #YourTable

    (

    YourTableID INT IDENTITY NOT NULL,

    FirstName VARCHAR(20) NOT NULL,

    LastName VARCHAR(20) NOT NULL,

    FullName AS FirstName + ' ' + ISNULL(STUFF(LastName,PATINDEX('% MD',LastName),3,''),LastName),

    Initial LastName AS SUBSTRING(FirstName,1,1) + ' ' + ISNULL(STUFF(LastName,PATINDEX('% MD',LastName),3,''),LastName)

    )

    ;

    --===== Populate the table with some of the previous test data

    -- including some of the odd names with "MD" in the name

    -- and after the name.

    INSERT INTO #YourTable

    (FirstName,LastName)

    SELECT 'John' ,'Hozack' UNION ALL

    SELECT 'Todd' ,'Toddingson' UNION ALL

    SELECT 'Jim' ,'Jimson' UNION ALL

    SELECT 'Agora' ,'Mdori' UNION ALL

    SELECT 'James' ,'Hamdor' UNION ALL

    SELECT 'Wilhelm' ,'van de Graff MD'

    ;

    --===== Show what we've got

    SELECT * FROM yourTable

    ;

    Even though that works, what are you going to do with first names that begin with a salutation, last names that end with other titles and suffixes, and first names that look like "Doctor & Mrs. Johnathan van Huron Esq. - DO, MD, MVP" ???? 😛

    Before you create any code for such a problem, you really need to identify the full extent of the problem.

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

  • "Doctor & Mrs. Johnathan van Huron Esq. - DO, MD, MVP"

    Title FName LName Degree

    Agree on Names but Titles & Degrees shouldn't be in source data.

  • Dev (12/21/2011)


    "Doctor & Mrs. Johnathan van Huron Esq. - DO, MD, MVP"

    Title FName LName Degree

    Agree on Names but Titles & Degrees shouldn't be in source data.

    "Shouldn't be" and "aren't" are two totally different worlds. We just saw the op point out that they are in the source data and you have to deal with it. Dealing with it may be getting the provider of the data to turn to and deliver proper data but I trust such providers about as far as I can throw a truck. 😉

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

Viewing 15 posts - 1 through 15 (of 20 total)

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