Query to split Firstname Lastname into 2 fields

21 Feb 2007 12:23 AM

Anthony Smith

Good Afternoon Everyone,

I hope everyone is doing GREAT today. I've got a database where my
customers First and Last name are in ONE field (ContactName), and we are
upgrading to another SQL application that actually has (2) seperate fields,
FirstName and LastName. Does anyone know how I can run a query to seperate
the First and Last name and put it into two fields?

Right now this is how the new SQL database is:

FieldNames
FirstName                    LastName
Anthony Smith

I imported the whole contactname field into the FirstName field. So
Lastname is blank. I'd like to take the last name from the 1st field and
put that into the LastName field.

This is what I'd like to acheive:
FieldNames
FirstName                    LastName
Anthony                       Smith

Thanks!

Sincerely,
Anthony Smith
In God We Trust!

21 Feb 2007 12:55 AM

Anith Sen

Are all the names names formatted the same? If so you can use CHARINDEX or
the LEFT & RIGHT fuctions like:

SELECT LEFT( @name , CHARINDEX( ' ', @name ) - 1 )
SELECT RIGHT( @name , CHARINDEX( ' ', REVERSE( @name ) ) - 1 )

If they are not formatted the same, you have some issues to ponder. What
should happen if there is a middle name or a middle initial? What if either
the firstname or the last name was missing? How would you address a part of
the name that has more than a single space in it? What about double
barrelled names?

--
Anith

21 Feb 2007 1:13 PM

Anthony Smith

Thank you everyone for the prompts replies. I think most of them are
formatted the same but there may be a few that aren't. If it takes care of
the bulk of the customers that'll be fine, we can manually change the rest.

Have a blessed day everyone!
Show quote

"Anith Sen" <an***@bizdatasolutions.com> wrote in message
news:uj1IsLVVHHA.4828@TK2MSFTNGP05.phx.gbl...
> Are all the names names formatted the same? If so you can use CHARINDEX or
> the LEFT & RIGHT fuctions like:
>
> SELECT LEFT( @name , CHARINDEX( ' ', @name ) - 1 )
> SELECT RIGHT( @name , CHARINDEX( ' ', REVERSE( @name ) ) - 1 )
>
> If they are not formatted the same, you have some issues to ponder. What
> should happen if there is a middle name or a middle initial? What if
> either the firstname or the last name was missing? How would you address a
> part of the name that has more than a single space in it? What about
> double barrelled names?
>
> --
> Anith
>

21 Feb 2007 1:45 AM

P. Ward

Hi Anthony

The following example should point you in the right direction. A couple of
things to watch out for are people that have two first names ie. Mary Jane
Smith and that the formatting of the data is consistent ie. no double spacing
etc.

CREATE TABLE Names
    (
        FirstName VARCHAR(20),
        LastName VARCHAR(20) NULL
    )

INSERT Names SELECT 'Anthony Smith', NULL
INSERT Names SELECT 'Peter Ward', NULL
INSERT Names SELECT 'John Brown', NULL
INSERT Names SELECT 'Prince', NULL
INSERT Names SELECT 'Mary Jane Smith', NULL

UPDATE    Names
SET    FirstName =
        CASE
            WHEN CHARINDEX(' ', FirstName) > 0 THEN SUBSTRING(FirstName, 1,
CHARINDEX(' ', FirstName) - 1)
            ELSE FirstName
        END,
    LastName =
        CASE WHEN CHARINDEX(' ', FirstName) > 0 THEN SUBSTRING(FirstName,
CHARINDEX(' ', FirstName) + 1, LEN(FirstName) - CHARINDEX(FirstName, ' '))
        END

SELECT * FROM Names

Returns:

FirstName            LastName
-------------------- --------------------
Anthony              Smith
Peter                Ward
John                 Brown
Prince               NULL
Mary                 Jane Smith

HTH

- Peter Ward
www.wardyit.com

Show quote

"Anthony Smith" wrote:

> Good Afternoon Everyone,
>
> I hope everyone is doing GREAT today. I've got a database where my
> customers First and Last name are in ONE field (ContactName), and we are
> upgrading to another SQL application that actually has (2) seperate fields,
> FirstName and LastName. Does anyone know how I can run a query to seperate
> the First and Last name and put it into two fields?
>
> Right now this is how the new SQL database is:
>
> FieldNames
> FirstName                    LastName
> Anthony Smith
>
> I imported the whole contactname field into the FirstName field. So
> Lastname is blank. I'd like to take the last name from the 1st field and
> put that into the LastName field.
>
> This is what I'd like to acheive:
> FieldNames
> FirstName                    LastName
> Anthony                       Smith
>
>
> Thanks!
>
> Sincerely,
> Anthony Smith
> In God We Trust!
>
>
>
>

21 Feb 2007 11:49 AM

Tibor Karaszi

.... and be prepared to manually scrub names such as:

John Steve St.Smith deWaal III

I haven't used such, but there are tools out these for this particular purpose. Depending on how
many names you have and the complexity of the names, such a tool might be cheaper in the end.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

Show quote

"Anthony Smith" <anth***@peconet.com> wrote in message
news:%23mepx5UVHHA.3948@TK2MSFTNGP05.phx.gbl...
> Good Afternoon Everyone,
>
> I hope everyone is doing GREAT today. I've got a database where my
> customers First and Last name are in ONE field (ContactName), and we are
> upgrading to another SQL application that actually has (2) seperate fields,
> FirstName and LastName. Does anyone know how I can run a query to seperate
> the First and Last name and put it into two fields?
>
> Right now this is how the new SQL database is:
>
> FieldNames
> FirstName                    LastName
> Anthony Smith
>
> I imported the whole contactname field into the FirstName field. So
> Lastname is blank. I'd like to take the last name from the 1st field and
> put that into the LastName field.
>
> This is what I'd like to acheive:
> FieldNames
> FirstName                    LastName
> Anthony                       Smith
>
>
> Thanks!
>
> Sincerely,
> Anthony Smith
> In God We Trust!
>
>
>

21 Feb 2007 11:57 AM

Uri Dimant

> John Steve St.Smith deWaal III
:-))

create table #t ( [Name] varchar(40))

insert into #t ([Name]) values ('Smith,John E')
insert into #t ([Name]) values ('Smith,Bill')
insert into #t ([Name]) values ('Smith,Adam F')
insert into #t ([Name]) values ('St,Smith deWaal III')

---go

select LastName, FirstName, MiddleName
from (
select
    Name,
    substring(Name,1,Comma-1) LastName,
    substring(Name,Comma+1,Spce-Comma-1) FirstName,
    nullif(substring(Name,Spce+1,40),'') MiddleName
from (
    select
      Name,
      charindex(',',Name) Comma,
      charindex(' ',Name+space(1),charindex(',',Name)) Spce
    from #t
) D
) SplitNames

drop table #t

Show quote

"Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in
message news:u0UwP5aVHHA.192@TK2MSFTNGP04.phx.gbl...
> ... and be prepared to manually scrub names such as:
>
> John Steve St.Smith deWaal III
>
> I haven't used such, but there are tools out these for this particular
> purpose. Depending on how many names you have and the complexity of the
> names, such a tool might be cheaper in the end.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
>
> "Anthony Smith" <anth***@peconet.com> wrote in message
> news:%23mepx5UVHHA.3948@TK2MSFTNGP05.phx.gbl...
>> Good Afternoon Everyone,
>>
>> I hope everyone is doing GREAT today. I've got a database where my
>> customers First and Last name are in ONE field (ContactName), and we are
>> upgrading to another SQL application that actually has (2) seperate
>> fields,
>> FirstName and LastName. Does anyone know how I can run a query to
>> seperate
>> the First and Last name and put it into two fields?
>>
>> Right now this is how the new SQL database is:
>>
>> FieldNames
>> FirstName                    LastName
>> Anthony Smith
>>
>> I imported the whole contactname field into the FirstName field. So
>> Lastname is blank. I'd like to take the last name from the 1st field and
>> put that into the LastName field.
>>
>> This is what I'd like to acheive:
>> FieldNames
>> FirstName                    LastName
>> Anthony                       Smith
>>
>>
>> Thanks!
>>
>> Sincerely,
>> Anthony Smith
>> In God We Trust!
>>
>>
>>
>
>