December 21, 2011 at 7:43 am
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
December 21, 2011 at 7:45 am
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?
December 21, 2011 at 7:48 am
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.
December 21, 2011 at 7:48 am
It's T-SQl
December 21, 2011 at 7:52 am
Thanks
December 21, 2011 at 7:58 am
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 ??/?
December 21, 2011 at 8:08 am
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
December 21, 2011 at 11:33 am
Very good script .i found which i was looking for.
December 21, 2011 at 1:48 pm
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
😎
December 21, 2011 at 9:28 pm
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
December 21, 2011 at 9:33 pm
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
December 21, 2011 at 9:36 pm
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
December 21, 2011 at 10:02 pm
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
December 21, 2011 at 10:19 pm
"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.
December 21, 2011 at 10:50 pm
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
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply