Posted by louisyoung187@hotmail.com on 03/09/07 14:05

On Mar 9, 10:46 am, masri...@gmail.com 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" <woos...@gmail.com> wrote:
>
>
>
> > 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

[Back to original message]