February 12, 2004 at 2:28 am
Hi Group,
I need to implement some logic in T-SQL for Microsoft SQL 7.0.
From a column names emp_name i need to retrive the first name,middle name and last name of the employee depending on the following conditions:
Data before first space = First Name
If data before second space is one position = Middle Name
If data before second space is more than one position = Last Name
Data after third space (if there is a third space) = Last Name
Some examples are:
emp_name has values as ABC
then First Name : ABC Middle Name : Blank Last Name : Blank
emp_name has values as ABC E FGH
then First Name : ABC Middle Name : E Last Name : FGH
emp_name has values as ABC EFG
then First Name : ABC Middle Name : Blank Last Name : EFG
emp_name has values as ABC E F GHI
then First Name : ABC Middle Name : E Last Name : GHI
emp_name has values as ABC EF GHI
then First Name : ABC Middle Name : BLANK Last Name : EF
As i am using a single select insert statement, would like if this logic can be done in a single statement,
Thanks Group
February 12, 2004 at 4:41 am
This is possible, I think...
Try using the CHARINDEX function to get the position of a space. With that value and the SUBSTRING function, you can get a part of a name...
So, for the firstname, that would be (replace the underscore by a space)
SUBSTRING(field, 1, CHARINDEX('_', field))
The middlename is a bit more difficult ...
The lastname is again more difficult, but I guess you have the idea now...
February 12, 2004 at 7:42 am
Horrible but....
SELECT LEFT(emp_name,CHARINDEX(' ',emp_name+' ')-1) AS 'FirstName',
(CASE
WHEN CHARINDEX(' ',emp_name,CHARINDEX(' ',emp_name)+1) = 0
THEN ''
WHEN (CHARINDEX(' ',emp_name,CHARINDEX(' ',emp_name)+1) -
CHARINDEX(' ',emp_name) - 1) = 1
THEN SUBSTRING(emp_name,CHARINDEX(' ',emp_name)+1,
(CHARINDEX(' ',emp_name,
CHARINDEX(' ',emp_name)+1) -
CHARINDEX(' ',emp_name) - 1))
ELSE ''
END) AS 'MiddleName',
(CASE
WHEN CHARINDEX(' ',emp_name) = 0
THEN ''
WHEN CHARINDEX(' ',emp_name,CHARINDEX(' ',emp_name)+1) = 0
THEN SUBSTRING(emp_name,
CHARINDEX(' ',emp_name)+1,LEN(emp_name) -
CHARINDEX(' ',emp_name))
WHEN CHARINDEX(' ',emp_name,
CHARINDEX(' ',emp_name,
CHARINDEX(' ',emp_name)+1)+1) = 0
THEN SUBSTRING(emp_name,
CHARINDEX(' ',emp_name,
CHARINDEX(' ',emp_name)+1)+1,LEN(emp_name) -
CHARINDEX(' ',emp_name,
CHARINDEX(' ',emp_name)+1))
ELSE SUBSTRING(emp_name,
CHARINDEX(' ',emp_name,
CHARINDEX(' ',emp_name,
CHARINDEX(' ',emp_name)+1)+1)+1,LEN(emp_name) -
CHARINDEX(' ',emp_name,
CHARINDEX(' ',emp_name,
CHARINDEX(' ',emp_name)+1)+1))
END) AS 'Lastname'
FROM
Far away is close at hand in the images of elsewhere.
Anon.
February 13, 2004 at 6:34 am
Not an answer to your question but your parsing algorithm fails for names like mine: Terri Santa Coloma, where Santa Coloma is my last name, no middle name. It also fails for 2 word last names like my friend, Mary Dee Johnson, where Mary Dee is the first name no middle name. I sure get a lot of strange ways my name is mangled by computer programs that don't have separate fields for first, middle and last name.
Terri
To speak algebraically, Mr. M. is execrable, but Mr. C. is
(x+1)-ecrable.
Edgar Allan Poe
[Discussing fellow writers Cornelius Mathews and William Ellery Channing.]
February 16, 2004 at 8:54 am
Thanks for that suggestion. I will keep that to mind. Anyway I will discuss with client also.
Anyway this is my final query: does it miss any of the condition specified by me:
SELECT emp_name,
CASE
WHEN CHARINDEX(' ',LTRIM(RTRIM(emp_name))) = 0 THEN emp_name
ELSE LEFT(LTRIM(RTRIM(emp_name)),CHARINDEX(' ',LTRIM(RTRIM(emp_name)))-1)
END AS 'First Name',
CASE
WHEN PATINDEX('% [a-z] %',LTRIM(RTRIM(emp_name))) = 0 THEN ''
ELSE SUBSTRING(LTRIM(RTRIM(emp_name)),PATINDEX('% [a-z] %',LTRIM(RTRIM(emp_name)))+1,1)
END AS 'Middle Name',
CASE
--when middle is of 1 char
WHEN PATINDEX('% [a-z] %',LTRIM(RTRIM(emp_name)))<>0 THEN
LTRIM(REVERSE(LEFT(REVERSE(LTRIM(RTRIM(emp_name))),CHARINDEX(' ',REVERSE(LTRIM(RTRIM(emp_name)))))))
ELSE
--when middle is of more than one char
CASE
WHEN len(SUBSTRING(LTRIM(RTRIM(emp_name)),CHARINDEX(' ',LTRIM(RTRIM(emp_name)))+1,CHARINDEX(' ',SUBSTRING(LTRIM(RTRIM(emp_name))+' ',CHARINDEX(' ',LTRIM(RTRIM(emp_name)))+1,LEN(LTRIM(RTRIM(emp_name)))))) )>1 THEN
SUBSTRING(LTRIM(RTRIM(emp_name)),CHARINDEX(' ',LTRIM(RTRIM(emp_name)))+1,CHARINDEX(' ',SUBSTRING(LTRIM(RTRIM(emp_name))+' ',CHARINDEX(' ',LTRIM(RTRIM(emp_name)))+1,LEN(LTRIM(RTRIM(emp_name))))))
ELSE
''
END
END AS 'LASTNAME1'
FROM employee
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply