Extract First Name From String

  • Follow


How can I extract the First Name only from the String Below? I only want to
pick up 'Veronica'
I can get the Last Name by using CHARINDEX(',',[Patient Name])-1


[Patient Name]   =     LUNA,VERONICA E

-- 
Brian Conner

Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200912/1

0
Reply Brian 12/9/2009 2:49:15 PM

Brian

SELECT LEFT(@Value, CHARINDEX(',', @Value)-1),



"Brian Conner via SQLMonster.com" <u47161@uwe> wrote in message 
news:a0558fa5b6081@uwe...
> How can I extract the First Name only from the String Below? I only want 
> to
> pick up 'Veronica'
> I can get the Last Name by using CHARINDEX(',',[Patient Name])-1
>
>
> [Patient Name]   =     LUNA,VERONICA E
>
> -- 
> Brian Conner
>
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200912/1
> 


0
Reply Uri 12/9/2009 2:59:15 PM

There could be many variations of a name and handling all cases can be very complex. Here is one query that will work 
with your sample data:

SELECT CASE WHEN CHARINDEX(',', [Patient Name]) > 0
             THEN SUBSTRING([Patient Name], CHARINDEX(',', [Patient Name]) + 1,
                         CASE WHEN CHARINDEX(' ', [Patient Name], CHARINDEX(',', [Patient Name]) + 1) > 0
                              THEN CHARINDEX(' ', [Patient Name], CHARINDEX(',', [Patient Name]) + 1) -
                                   (CHARINDEX(',', [Patient Name]) + 1)
                              ELSE LEN([Patient Name]) - CHARINDEX(',', [Patient Name])
                         END)
        END AS first_name
FROM Patients;

-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Reply Plamen 12/9/2009 3:35:20 PM

2 Replies
736 Views

(page loaded in 0.036 seconds)


Reply: