Remove and split data into multiple columns in select statement.

Question

I have data like following table

I want to remove Titles ( Mr. Miss, Dr etc) from name and want to split data into First name and last name if two names exists.

I want this in select statement. I can remove title using CASE statement but unable to split name into tow in same case statement.

I want data like this but in select statement, title removed and name splitted.

Answer 1

You can do this pretty easy with substring. You want to split the string by spaces, then ignore the first element in the index, roughly like this:

SELECT SUBSTRING(@ourName, 1, CHARINDEX(' ', @ourName)) AS [First],
SUBSTRING(@ourName, CHARINDEX(' ', @ourName) + 2, LEN(@ourName)) AS[Last]

Not tested, but that's pretty close to what you want to do. You'll be breaking the string into an array like:

[0] Prefix [1] First Name [2] Last Name

And only grabbing 1 and 2. This will break, however if there is no prefix.

Answer 2

You can combine the Jeremy answer with concatenated CASE statements to acomplish your requirement. It will be a quite complicated sentence but it can works.

Answer 3

Try the following UDF

create function dbo.ExtractName(@TheName VARCHAR(200),@Which CHAR(1) )
returns VARCHAR(100)
as
begin
    declare @Ans VARCHAR(100)
    -- Get rid of common saluations
    SET @theName = replace(replace(@theName,'mr.',''),'mrs.','')
    SET @theName = replace(replace(@theName,'ms',''),'miss','')
    SET @theName = replace(replace(@theName,'dr.',''),'sir','')

    SET @Ans = rtrim(ltrim(@theName))+'  '

    -- Assume last name
    if @Which = 'L'
     set @ans = rtrim(substring(@ans,charindex(' ',@ans)+1,99))
    else
     set @ans = left(@ans,charindex(' ',@ans)-1)

    if len(@ans)='' set @ans= null

    return @ans
end
go
print dbo.ExtractName('Mr. Rick Pepper','F')
print dbo.ExtractName('Mr. Rick Pepper','L')

Extracting names can be very complex, since there are a large number of possible prefixes, sometimes names are stored last, first. Some names have suffixes, like Jr. or PHD. Hopefully this UDF gives you a starting point...

Answer 4

Try this

declare @tbl table (GoodName varchar(50)) 
insert into @tbl select 'Mr.Rick Pepper' 
insert into @tbl select 'Miss  Lara Harper' 
insert into @tbl select 'Mrs Kim' 
insert into @tbl select 'Dr.Alan White' 
insert into @tbl select 'Adam Jones' 
insert into @tbl select 'William' 
insert into @tbl select 'Sir Clark'

--Program Starts

select 
 case when CHARINDEX(',',FilteredName) = 0 then FilteredName else SUBSTRING(FilteredName,0,CHARINDEX(',',FilteredName)) end as FirstName 
 ,case when CHARINDEX(',',FilteredName) = 0 then Null else SUBSTRING(FilteredName,CHARINDEX(',',FilteredName)+1,LEN(FilteredName)) end as LastName 
 from (
 select REPLACE(LTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(GoodName,'Mr.',''),'Miss',''),'Mrs',''),'Dr.',''),'Sir','')),' ',',') as FilteredName
 from @tbl
 )x(FilteredName)

Output

FirstName LastName

Rick Pepper
Lara Harper
Kim NULL
Alan White
Adam Jones
William NULL
Clark NULL