dkim18
asked on
Select on Dates and parsing?
Hi,
Say I have the following table:
TableEmployees(ID,FullName
1) I want to find records by Dates falling on Startdate or Promotedate.
Ex) I want records who started on oct-nov 2009 or who promoted on oct-nov 2009
select ID, FullName,Salary
From Table
Where sex IN (@sex) and (StartDate <= @sdate and StartDate > @sdate OR
promotedate <= @sdate and PromoteDate > @sdate)
2) I want to parse out last name only from FullName.
The full name is in this format ((lastname)) firstname
How can I parse out lastname in sql?
Say I have the following table:
TableEmployees(ID,FullName
1) I want to find records by Dates falling on Startdate or Promotedate.
Ex) I want records who started on oct-nov 2009 or who promoted on oct-nov 2009
select ID, FullName,Salary
From Table
Where sex IN (@sex) and (StartDate <= @sdate and StartDate > @sdate OR
promotedate <= @sdate and PromoteDate > @sdate)
2) I want to parse out last name only from FullName.
The full name is in this format ((lastname)) firstname
How can I parse out lastname in sql?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
probably if there is only a space between the last name and first name then you can search for the space and split it up, this might not work when people have 2 names or 2 last names
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
For my FullName field, it always starts with ((lastname)) firstname because that's how it got set up.
I am trying to find out if this can be parsed out without too much effort.
Thanks.
I am trying to find out if this can be parsed out without too much effort.
Thanks.
I suppose you could take everything before the first " " and call it a first name, and everything after a last name .... still pretty dodgy.
ASKER
Correction:
It can be either
((lastname)), firstname or ((lastname)) firstname.
It can be either
((lastname)), firstname or ((lastname)) firstname.
if the last name contains the parentesis then tigin44 code shold work
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
check my post... that gives the lastname also...
tigin, it includes the )), you need to do -1 instead of -3.
MY date logic doesn't take time into account but your does; from his question it looks like he isnt interested in time.
MY date logic doesn't take time into account but your does; from his question it looks like he isnt interested in time.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The above script will correct fullname under these circumstances:
'Surname FirstName' and
lastname, name
'Surname FirstName' and
lastname, name
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
i think i will depend how you maintain the records or the persons in charge of it, one of the things i have found througout my years in systems is that you cannot control in a system without rules how people write down information
for example the most common are address fields some people will use
apartment, apt, ap, or use cty, county, city, ln, lane, drv, drive, ctr, center, crt, court some will put #, no. num, n its a hell of a job cleaning this but the best way to do it is by first cleaning existing records, then enforcing field rules, you said you have lastname, name or lastname firstname, are you sure there are no lastname firstname middle name or something either way the answers from my fellow colleague experts will definitely help you out, it me not be 100% because human nature is not 100% accurate when data entry with no rules is enforced.
Ok i said it, thank you to all and lets see what the author has on its reply, i hope you guys solve this already!!
for example the most common are address fields some people will use
apartment, apt, ap, or use cty, county, city, ln, lane, drv, drive, ctr, center, crt, court some will put #, no. num, n its a hell of a job cleaning this but the best way to do it is by first cleaning existing records, then enforcing field rules, you said you have lastname, name or lastname firstname, are you sure there are no lastname firstname middle name or something either way the answers from my fellow colleague experts will definitely help you out, it me not be 100% because human nature is not 100% accurate when data entry with no rules is enforced.
Ok i said it, thank you to all and lets see what the author has on its reply, i hope you guys solve this already!!
FirstName LastName and not Lastname FirstName in some cases ?