April 25, 2017 at 7:50 am
I have a column FullName with data as follows:
John Doe
Joe Doe
Jane Doe
I want a result in a new column FullNameNew as follows
Doe, John
Doe, Joe
Doe, Jane
What query script will give me this result?
Thanks for your help.
April 25, 2017 at 7:57 am
This has the potential to be challenging, because how would you handle a name like John Paul Revere? I can only think of one effective and performant way to do this, and it involves the use of a string-splitter function that Jeff Moden authored way back when SQL 2008 was the current version of SQL Server. Take a good look at the following article he wrote:
http://www.sqlservercentral.com/articles/72993/
You'll want to be using CROSS APPLY together with the DelimitedSplit8K function.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 25, 2017 at 8:05 am
nickman - Tuesday, April 25, 2017 7:50 AMI have a column FullName with data as follows:
John Doe
Joe Doe
Jane DoeI want a result in a new column FullNameNew as follows
Doe, John
Doe, Joe
Doe, JaneWhat query script will give me this result?
Thanks for your help.
As Steve said, this is close to impossible to get it right. How would you handle my full name: Luis Alonso Cazares Nuñez? Your best option is to store First Name and Last Name in separate columns, then display them as needed.
April 25, 2017 at 8:08 am
I have checked the data for the change and it all is in the format described
First names that are more than one name are separated by a hyphen.
So in this case names with more than Firstt Name last name do not apply in this case.
There are only about a thousand records.
Thanks for your response.
April 25, 2017 at 8:10 am
As Steve said, the problem here is that you may have to "guess" where to split the name. The reason being, is that the last "word" of the name might not be the surname.
For example, if we take the name "John Joe Bloggs", you could split this as "Bloggs, John Joe" or "Joe Bloggs, John". Which is correct; well that would be "Bloggs, John Joe".
Now, let's take a different name "Marine Le Pen" (one of current French presidential front runners). Now, again, this could be split to "Pen, Marine Le", or "Le Pen, Marine". The correct answer this time, however, is "Le Pen, Marine". This is the opposite logic to "John Joe Bloggs".
There is no correct answer to this question, unfortunately. In an ideal word, names really need to be stored in several parts (Title, Forename(s), Middle Name(s), Surname(s)), and yes, all of those can be plurals. If you have to do this, make a decision on what way you're going to split the data, and do so (keep your original data). Then have someone audit the data, or double check it before any thing that needs to be correct is sent. If the data is incorrect, ensure you have a retification plan.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 25, 2017 at 8:36 am
Warnings were made, so here's a possibility.
SELECT SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, 8000) + ', '
+ LEFT(FullName, CHARINDEX(' ', FullName) - 1)
FROM (VALUES
('John Doe'),
('Joe Doe'),
('Jane Doe'))x(FullName)
April 26, 2017 at 5:19 am
And then you may have a name like Arthur Conan Doyle. His given last name was Doyle, he was knighted under the name Doyle, but sometimes Conan Doyle is used as his last name.
Or the composer Ralph Vaughn Williams - his last name is Vaughn Williams.
April 26, 2017 at 6:09 am
a wordier version just like what Luis did;
i thought this way shows the advantage of actually storing the data correctly.
I added the three name examples from our other fine posters examples for reference
with NotAGoodSample(FullName)
AS
(
SELECT 'John Doe' UNION ALL
SELECT 'Joe Doe' UNION ALL
SELECT 'Jane Doe' UNION ALL
SELECT 'John Paul Revere' UNION ALL
SELECT 'Jeff Moden' UNION ALL
SELECT 'John Joe Bloggs' UNION ALL
SELECT 'Marine Le Pen' UNION ALL
SELECT 'Arthur Conan Doyle' UNION ALL
SELECT 'Ralph Vaughn Williams'
),
SplitValues(FirstName,LastName)
AS
(
SELECT
LEFT(FullName, CHARINDEX(' ', FullName) - 1) As FirstName,
SUBSTRING(FullName, CHARINDEX(' ', FullName) + 1, 8000) AS LastName
FROM NotAGoodSample
)
SELECT LastName + ', ' + FirstName As NewFormat,*
FROM SplitValues
Lowell
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply