First name And last Name in SQL

Question

Post reply

First name And last Name in SQL

bhaveshp.dba

SSC Eights!

Points: 834
More actions
December 21, 2011 at 7:43 am

#391177

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

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

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

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 1

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?

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

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/

bhaveshp.dba SSC Eights! Points: 834 More actions · Answer 3

bhaveshp.dba

SSC Eights!

Points: 834

December 21, 2011 at 7:48 am

#1424242

It's T-SQl

bhaveshp.dba SSC Eights! Points: 834 More actions · Answer 4

bhaveshp.dba

SSC Eights!

Points: 834

December 21, 2011 at 7:52 am

#1424250

Thanks

bhaveshp.dba SSC Eights! Points: 834 More actions · Answer 5

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 ??/?

Cadavre SSC-Forever Points: 41690 More actions · Answer 6

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/

SQlLogic SSCrazy Eights Points: 9686 More actions · Answer 7

SQlLogic

SSCrazy Eights

Points: 9686

December 21, 2011 at 11:33 am

#1424389

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

x002548 SSC Enthusiast Points: 112 More actions · Answer 8

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

😎

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

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)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 10

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)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 11

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)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 12

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)

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 13

"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.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 14

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)