[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 224
  • Last Modified:

Select on Dates and parsing?

Hi,
Say I have the following table:
TableEmployees(ID,FullName,sex, StartDate,PromoteDate,Salary)

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?
0
dkim18
Asked:
dkim18
  • 4
  • 4
  • 2
  • +3
5 Solutions
 
fhillyer1Commented:
try
select ID, FullName,Salary
From Table
Where sex IN (@sex) and ((StartDate <= @sdate and StartDate > @sdate)OR 
(promotedate <= @sdate and PromoteDate > @sdate))

Open in new window

0
 
zadeveloperCommented:
if you only have a field called full name how do you know if the data is in the format:
FirstName LastName and not Lastname FirstName in some cases ?
0
 
fhillyer1Commented:
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
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
tigin44Commented:
try
SELECT ID, FullName,Salary, SUBSTRING(FullName, 3, CHARINDEX('))', FullName) -3 ) AS lastName
From Table
Where sex IN (@sex) and ((StartDate <= @sdate and StartDate > @sdate)OR 
(promotedate <= @sdate and PromoteDate > @sdate))

Open in new window

0
 
dkim18Author Commented:
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.
0
 
zadeveloperCommented:
I suppose you could take everything before the first " " and call it a first name, and everything after a last name .... still pretty dodgy.
0
 
dkim18Author Commented:
Correction:
It can be either
((lastname)), firstname or ((lastname)) firstname.
0
 
fhillyer1Commented:
if the last name contains the parentesis then tigin44 code shold work
0
 
igni7eCommented:
select substring(fullname, 3, charindex('))', FullName)-1)
from table
where sex in (@sex) and dateadd(day, 0, @sdate) between dateadd(day, 0, StartDate)  and dateadd(day, 0, promotedate)
0
 
tigin44Commented:
check my post... that gives the lastname also...
0
 
igni7eCommented:
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.
0
 
zadeveloperCommented:
This should take care of the firstname last name fields as per your requirements.

select 	
	 case when CHARINDEX(',', ltrim(rtrim(FullName)), 0) = 0 then 
			 ltrim(rtrim(substring(ltrim(rtrim(FullName)), 0, (CHARINDEX(' ', ltrim(rtrim(FullName)) , 0))))) 
		else ltrim(rtrim(substring(ltrim(rtrim(FullName)), (CHARINDEX(',', ltrim(rtrim(FullName)) , 0)+1), LEN(ltrim(rtrim(FullName)))-(CHARINDEX(',', ltrim(rtrim(FullName)) , 0) - 1) ))) end as LastName,
	 
	 case when CHARINDEX(',', ltrim(rtrim(FullName)), 0) = 0 then 
			ltrim(rtrim(substring(ltrim(rtrim(FullName)), (CHARINDEX(' ', ltrim(rtrim(FullName)) , 0)), LEN(ltrim(rtrim(FullName)))-(CHARINDEX(' ', ltrim(rtrim(FullName)) , 0) - 1) ))) 
		else ltrim(rtrim(substring(ltrim(rtrim(FullName)), 0, (CHARINDEX(',', ltrim(rtrim(FullName)) , 0)))))  end as FirstName
from table
where sex in (@sex) and dateadd(day, 0, @sdate) between dateadd(day, 0, StartDate)  and dateadd(day, 0, promotedate)

Open in new window

0
 
zadeveloperCommented:
The above script will correct fullname under these circumstances:

'Surname FirstName' and
lastname, name
0
 
awking00Commented:
The following substrings the fullname up to the space, trims any following comma that may exist, and removes any pair of opening or closing parentheses:
replace(replace(rtrim(substring(fullname,1,charindex(' ',fullname) - 1)','),'(('),'))')
0
 
fhillyer1Commented:
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!!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 4
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now