Prev: Delete Primary transaction Log
Next: SQL Server 2000 Disable Ad-Hoc Access
From: Brian Conner via SQLMonster.com on 9 Dec 2009 09:49
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

From: Uri Dimant on 9 Dec 2009 09:59
Brian

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



"Brian Conner via SQLMonster.com" <u47161(a)uwe> wrote in message
news:a0558fa5b6081(a)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
>


From: Plamen Ratchev on 9 Dec 2009 10:35
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
 | 
Pages: 1
Prev: Delete Primary transaction Log
Next: SQL Server 2000 Disable Ad-Hoc Access