Parsing Text

Dear All,
I know how to parse this in ACCESS but not clear how to do this through
stored procedure. I Googled with different search criteria but didn't get an
y
useful hit. Any one have something handy?
I would like to parse:
"strFullName" listed as Habibullah, Mohammad I to three fields as:
strLastName: Habibullah
strFirstName: Mohammad
strMiddleName: I
Or another example, I would like "strFullName" listed as Huang, Chong Xi to
parse as:
strLastName: Huang
strFirstName: Chong
strMiddleName: Xi
Assuming that table name is User and field name is strFullName.
Thanks.
Habibullah.Habibullah,
You can use the T-SQL string functions to achieve your goal. See String
Functions in the SQL BOL. (esp. CHARINDEX and SUBSTRING). Ideally you'd
want to perform this string manipulation at the middle-tier or on the
client.
HTH
Jerry
"Habibullah" <Habibullah@.discussions.microsoft.com> wrote in message
news:18A25EB1-FE23-400B-82B1-3ED108B1B8DD@.microsoft.com...
> Dear All,
> I know how to parse this in ACCESS but not clear how to do this through
> stored procedure. I Googled with different search criteria but didn't get
> any
> useful hit. Any one have something handy?
> I would like to parse:
> "strFullName" listed as Habibullah, Mohammad I to three fields as:
> strLastName: Habibullah
> strFirstName: Mohammad
> strMiddleName: I
>
> Or another example, I would like "strFullName" listed as Huang, Chong Xi
> to
> parse as:
> strLastName: Huang
> strFirstName: Chong
> strMiddleName: Xi
> Assuming that table name is User and field name is strFullName.
> Thanks.
> Habibullah.|||Hi
One way to do it would be to use
SELECT
LEFT(strFullName,CHARINDEX(',',strFullNa
me)-1) AS strLastname,
LTRIM(SUBSTRING(strFullName,CHARINDEX(',
',strFullName)+1,LEN(strFullName)-CH
ARINDEX('
',REVERSE(strFullName))-CHARINDEX(',',strFullName))) AS strFirstname,
RIGHT(strFullName,CHARINDEX(' ',REVERSE(strFullName))-1) AS strMiddlename
FROM User
But this depends on how consistent the data is.
John
"Habibullah" <Habibullah@.discussions.microsoft.com> wrote in message
news:18A25EB1-FE23-400B-82B1-3ED108B1B8DD@.microsoft.com...
> Dear All,
> I know how to parse this in ACCESS but not clear how to do this through
> stored procedure. I Googled with different search criteria but didn't get
> any
> useful hit. Any one have something handy?
> I would like to parse:
> "strFullName" listed as Habibullah, Mohammad I to three fields as:
> strLastName: Habibullah
> strFirstName: Mohammad
> strMiddleName: I
>
> Or another example, I would like "strFullName" listed as Huang, Chong Xi
> to
> parse as:
> strLastName: Huang
> strFirstName: Chong
> strMiddleName: Xi
> Assuming that table name is User and field name is strFullName.
> Thanks.
> Habibullah.