CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008
StealthRT
StealthRT

Reputation: 10542

SQL query Lastname, first name within string

Hey all I have a string that is formatted like so:

Bob Barker - The Game Show Host
Bill Gates - A Programmer
Steve Jobs - The Apple Guy

The above is in a row called rowNameOcu.

What I am looking to do is query that and present it like this:

Barker, Bob - The Game Show Host
Gates, Bill - A Programmer
Jobs, Steve - The Apple Guy

I am sure I could look for the - and then grab everything to the right of that, but then how can I reverse the name after that?

Any help would be great!

Upvotes: 0

Views: 104

Answers (2)

DonTomasino
DonTomasino

Reputation: 36

Here's an option:

SELECT  
SUBSTRING(rowNameOcu,CHARINDEX(' ',rowNameOcu),CHARINDEX(' ',right(rowNameOcu,len(rowNameOcu) - CHARINDEX(' ',rowNameOcu)))) + ', ' + left(rowNameOcu,CHARINDEX(' ',rowNameOcu)) + RIGHT(rowNameOcu, CHARINDEX('-',reverse(rowNameOcu)))  
FROM MyTable

Upvotes: 2

Lukasz Szozda
Lukasz Szozda

Reputation: 175596

You can use CHARINDEX and SUBSTRING:

SqlFiddleDemo

Declare @t table ( [Name] varchar(100) );

insert into @t ( Name )
VALUES ( 'Bob Barker - The Game Show Host' ),
      ('Bill Gates - A Programmer'),
      ('Steve Jobs - The Apple Guy') ;

;WITH cte AS
(SELECT
    [Name],
    [Title] = CASE WHEN CHARINDEX('-', [Name]) > 0 THEN
                       SUBSTRING([Name],CHARINDEX('-',[Name])+1, ( LEN([Name]) - CHARINDEX(' ',[Name])+1) )
                       ELSE NULL
                   END,   
    [FIRST_NAME] = CASE WHEN CHARINDEX(' ', [Name]) > 0 THEN
                      LEFT([Name],CHARINDEX(' ',[Name])-1)
                      ELSE[Name]
                    END,
    [LAST_NAME] = CASE WHEN CHARINDEX(' ', [Name]) > 0 THEN
                       SUBSTRING([Name],CHARINDEX(' ',[Name])+1, (CHARINDEX('-',[name]) - CHARINDEX(' ', [name])-1) )
                       ELSE NULL
                   END 
FROM @t
)
SELECT [Last_Name] + ', ' + [First_Name] + ' - ' + [Title]
FROM cte;

Warning: They may be edge cases when it fails like 3 part name or title with -

Upvotes: 0

Related Questions