CODEWITHSUNDEEP
sqlsql-serversql-server-2008parsing
kevorski
kevorski

Reputation: 836

parsing first and last names from sql table

I have a column in my SQL Server 2008 database that contains c/o Full Name. I'm having issues getting the First and Last names separated. I have the c/o in its own column, but I need the First/Last Names. I know that there is an issue with the second and third case statement, but I'm not sure what I need to change. Should I be storing index variables somewhere to reference them?

Declare @t table ( [name] varchar(100) )

INSERT INTO @t ( Name )
SELECT Street1 FROM tblPeople
WHERE CO = 'Y' AND LastName NOT LIKE 'SUMMERHAVEN INC' AND Street1 Like '%c/o%'

SELECT
    [name],
    CHARINDEX(' ', [name]),
    CASE WHEN CHARINDEX(' ', [name]) > 0 THEN
        LEFT([name],CHARINDEX(' ',[name])-1)
    ELSE
        [name]
    END as CO,
    CASE WHEN CHARINDEX(' ', [name]) > 0 THEN
        SUBSTRING([name],CHARINDEX(' ',[name])+1, ( LEN([name]) - CHARINDEX(' ',[name])+1) )
    ELSE
        NULL
    END as FIRST_NAME,
    CASE WHEN CHARINDEX(' ', [name]) > 0 THEN
        SUBSTRING([name],CHARINDEX(' ',[name])+1, ( LEN([name]) - CHARINDEX(' ',[name])+1) )
    ELSE
        NULL
    END as LAST_NAME
FROM @t

Upvotes: 1

Views: 126

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 81930

To my surprise, PARSENAME() was available in 2008

Consider the following

Declare @YourTable table (Name varchar(50))
Insert Into @YourTable values 
 ('c/o Cher')
,('c/o John Smith')
,('John Smith')

Select *
      ,FirstName=parsename(replace(ltrim(rtrim(replace(name,'c/o',''))),' ','.'),2)
      ,LastName =parsename(replace(ltrim(rtrim(replace(name,'c/o',''))),' ','.'),1)
 From @YourTable

Returns

Name            FirstName   LastName
c/o Cher        NULL        Cher
c/o John Smith  John        Smith
John Smith      John        Smith

Upvotes: 2

JohnLBevan
JohnLBevan

Reputation: 24410

Here's an approach using Common Table Expressions which so that the logic for finding the spaces to break on is reasonably self-documenting.

Declare @t table ( [name] varchar(100) )

insert into @t ( Name )
select 'one two three'
union select 'a bc def'
union select 'one two'
union select 'one'

;with cte1 as 
(
    select name
    ,charindex(' ', name) FirstSpace
    from @t
)
, cte2 as (
    select name
    , FirstSpace
    ,charindex(' ', name, FirstSpace+1) SecondSpace
    from cte1
)
select name
, FirstSpace
, SecondSpace
, case when FirstSpace=0 then name else substring(name, 1, FirstSpace-1) end Company
, case when SecondSpace=0 then null else substring(name, FirstSpace+1, SecondSpace-FirstSpace-1) end FirstName
, case when SecondSpace=0 or SecondSpace=len(name) then null else substring(name, SecondSpace+1, len(name)-SecondSpace) end LastName
from cte2

Upvotes: 1

Renats Stozkovs
Renats Stozkovs

Reputation: 2605

Disclosure: this is a bad idea to parse the data like this, but here you go:

Declare @t table ( [name] varchar(100) )

insert into @t ( Name ) values ('c/o Full Name')

SELECT
CASE WHEN CHARINDEX(' ', [name]) > 0 THEN
    LEFT([name],CHARINDEX(' ',[name])-1)
ELSE
    [name]
END as CO,
CASE WHEN CHARINDEX(' ', [name]) > 0 THEN
    SUBSTRING([name],CHARINDEX(' ',[name])+1, CHARINDEX(' ',[name]) )
ELSE
    NULL
END as FIRST_NAME,
CASE WHEN CHARINDEX(' ', [name]) > 0 THEN
    SUBSTRING([name],CHARINDEX(' ',[name], CHARINDEX(' ',[name])+1), len([name]))
ELSE
    NULL
END as LAST_NAME
FROM @t

Upvotes: 1

Related Questions