Parse fields

[portCo]

Hi there,

I am re-organizing the database. We used to have field 'names' in our
table for our first name and last name. However, I want to have those
names in different field.

FYI, I have two different tables in different databases. The A
database contains A table with 'names" field. The B database contains
B table with 'fname' and 'lname' I want to copy data A table to B
table.

How can I parse names field into first name and last name fields?

Here are some examples which are in the database.
(
id names
01 John Doe
02 John & Jane Doe
03 Mr & Mrs Doe
) something like this. It might contain '&' and two names. If there
are two names, then use first name.
Thanks guys,

[masri999]

Do something like this
declare @tbla table ( id int ,names varchar(100))
insert into @tbla values (1,'John Doe')
insert into @tbla values (1,'John & Jane Doe')
insert into @tbla values (1,'Mr & Mrs Doe ')

select
id,
firstname = case when names like '%&%' then names
else
case when charindex(' ',names) 0
then substring(names,1,charindex(' ',names) - 1)
else names
end
end ,
lastname = case when names like '%&%' then ''
else
case when charindex(' ',names) 0
then substring(names,charindex(' ',names)+ 1,len(names) -
charindex(' ',names))
else names
end
end

from @tbla

M A Srinivas


On Mar 9, 1:20 pm, "portCo" <[e-mail]:

Hi there,

I am re-organizing the database. We used to have field 'names' in our
table for our first name and last name. However, I want to have those
names in different field.

FYI, I have two different tables in different databases. The A
database contains A table with 'names" field. The B database contains
B table with 'fname' and 'lname' I want to copy data A table to B
table.

How can I parse names field into first name and last name fields?

Here are some examples which are in the database.
(
id names
01 John Doe
02 John & Jane Doe
03 Mr & Mrs Doe
) something like this. It might contain '&' and two names. If there
are two names, then use first name.

Thanks guys,

[louisyoung187]

On Mar 9, 10:46 am, [e-mail] wrote:

Do something like this
declare @tbla table ( id int ,names varchar(100))
insert into @tbla values (1,'John Doe')
insert into @tbla values (1,'John & Jane Doe')
insert into @tbla values (1,'Mr & Mrs Doe ')

select
id,
firstname = case when names like '%&%' then names
else
case when charindex(' ',names) 0
then substring(names,1,charindex(' ',names) - 1)
else names
end
end ,
lastname = case when names like '%&%' then ''
else
case when charindex(' ',names) 0
then substring(names,charindex(' ',names)+ 1,len(names) -
charindex(' ',names))
else names
end
end

from @tbla

M A Srinivas

On Mar 9, 1:20 pm, "portCo" <[e-mail]:

Hi there,

I am re-organizing the database. We used to have field 'names' in our
table for our first name and last name. However, I want to have those
names in different field.

FYI, I have two different tables in different databases. The A
database contains A table with 'names" field. The B database contains
B table with 'fname' and 'lname' I want to copy data A table to B
table.

How can I parse names field into first name and last name fields?

Here are some examples which are in the database.
(
id names
01 John Doe
02 John & Jane Doe
03 Mr & Mrs Doe
) something like this. It might contain '&' and two names. If there
are two names, then use first name.

Thanks guys,- Hide quoted text -

- Show quoted text -

The following will also work:

SELECT id,
names,
RTRIM(SUBSTRING(names, 1, CHARINDEX(' ', names) - 1)) fname,
CASE
WHEN CHARINDEX('&', names) 0 THEN
LTRIM(SUBSTRING(names, CHARINDEX('&', names) + 1, LEN(names)))
ELSE
LTRIM(SUBSTRING(names, CHARINDEX(' ', names), LEN(names)))
END lname
FROM tblA

[portCo]

On Mar 9, 12:20 am, "portCo" <[e-mail]:

Hi there,

I am re-organizing the database. We used to have field 'names' in our
table for our first name and last name. However, I want to have those
names in different field.

FYI, I have two different tables in different databases. The A
database contains A table with 'names" field. The B database contains
B table with 'fname' and 'lname' I want to copy data A table to B
table.

How can I parse names field into first name and last name fields?

Here are some examples which are in the database.
(
id names
01 John Doe
02 John & Jane Doe
03 Mr & Mrs Doe
) something like this. It might contain '&' and two names. If there
are two names, then use first name.

Thanks guys,

Thanks guys

[--CELKO--]

>How can I parse names field [sic] into first name and last name fields [sic]? <<

Don't do it. Name handling is an ugly problem and if you have to do
this on a regular basis get a package designed for this kind of work.
Some companies are Group 1 Software, SSA (used to have a great booklet
on this topic), Melissa Data Corporation and Promark Software Inc.

Their software handles mailing lists and you can get a review copy
from Melissa Data. They do not choke on names like "John Paul van der
Poon" and worse.

[othellomy]

On Mar 9, 2:20 pm, "portCo" <[e-mail]:

Hi there,

I am re-organizing the database. We used to have field 'names' in our
table for our first name and last name. However, I want to have those
names in different field.

FYI, I have two different tables in different databases. The A
database contains A table with 'names" field. The B database contains
B table with 'fname' and 'lname' I want to copy data A table to B
table.

How can I parse names field into first name and last name fields?

Here are some examples which are in the database.
(
id names
01 John Doe
02 John & Jane Doe
03 Mr & Mrs Doe
) something like this. It might contain '&' and two names. If there
are two names, then use first name.

Thanks guys,

hi,

select id,
fname = substring(ltrim(rtrim(names)),1,charindex('
',ltrim(rtrim(names))) - 1),
lname = reverse(substring(reverse(ltrim(rtrim(names))),
1,charindex(' ',reverse(ltrim(rtrim(names)))) - 1))
from Atable

HTH