July 12, 2009 at 6:46 am
quick assistance, i need to write a query to show a list of names of people who have terminated for the AD administrator.
we have lastname firstnames ie smith(lastname) george adams (first names)
I need to separate the firstnames into two columns if they have two names.
ie select lastname, firstnames termination date from employees where termination date >=......
there is one space consistently between the two names.
so far i have this
select *
from (
select lastname,
case when substring(firstname,' ') > 0 then substring(firstname,1, substring(firstname,' ')-1) else firstname end firstname_1,
case when substring(firstname,' ') > 0 then substring(firstname, substring(firstname,' ')+1) else null end firstname_2,
termination_date >= 2009-01-01
from employees
not got the substring right.
where termination_date >= '01-may-2008'
July 12, 2009 at 7:19 am
Hello,
I suspect you want to be using CharIndex to find the location of the Space and then SubString to extract the Firstnames.
So may be something along the lines of:-
Select
lastname,
case when CharIndex(' ', firstname) > 0 Then substring(firstname,1, CharIndex(' ', firstname)-1) else firstname end firstname_1,
case when CharIndex(' ', firstname) > 0 then Right(firstname, CharIndex(' ', firstname)+1) else null end firstname_2
From
employees
Where
termination_date >= '01-may-2008'
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
July 13, 2009 at 2:52 am
the results i am getting on the first couple of sample results
lastname firstname_1 firstname_2
Davie Michael ael James
Eyears Barry NULL
Murrie Amelia ia Grace
can we get rid of the first 3 on firstname_2
and how
almost there
July 13, 2009 at 3:42 am
Hey there is a slight modification in the previous code
just apply this code
Select
lastname,
case when CharIndex(' ', firstname) > 0 Then substring(firstname,1, CharIndex(' ', firstname)-1) else firstname end firstname_1,
case when CharIndex(' ', firstname) > 0 then Right(firstname, CharIndex(' ', firstname)-1) else null end firstname_2
From
employees
Where
termination_date >= '01-may-2008'
here the right function since counts from last you have to again decrease the count by 1
July 14, 2009 at 4:15 am
Hello again,
Did you manage to get the query working Okay, or are you still having problems?
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
July 14, 2009 at 7:47 pm
not quite
running the last query you gave me, had to modify column names for our database
Select
lastname,
case when CharIndex(' ', firstnames) > 0 Then substring(firstnames,1, CharIndex(' ', firstnames)-1) else firstnames end firstnames_1,
case when CharIndex(' ', firstnames) > 0 then Right(firstnames, CharIndex(' ', firstnames)-1) else null end firstnames_2,
terminationdate
From
employee
Where
terminationdate >= '2009-05-01 00:00:00.000'
still getting the spaces and last letter from firstname attached is a sample.
I have checked the application and they are only putting one space between.
the column firstname is varchar 30 characters.
look forward to further assistance and grateful for your help
July 15, 2009 at 2:56 am
Hello,
You could try replacing the Right Function with the following:-
substring(firstname, CharIndex(' ', firstname)+1, 200)
Note that this code assumes a first name is never longer than 200 characters.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
July 17, 2009 at 8:49 am
You could try this, which would eliminate the CASE. There are probably some instances that would break it, but for cases where there is never a space in the first position of first name, and there is only one space between the two names, I think it will work ok. In fact, as I'm typing now, I can think of a co-workers name that would break it. His first name is 'De Andre'. I don't know how you could get around this kind of name. Anyhow, let us know if it works for you.
IF OBJECT_ID('TempDB..#names','u') IS NOT NULL
DROP TABLE #names
CREATE TABLE #names
(
ID INT IDENTITY(1,1),
LAST_NAME VARCHAR(30),
FIRST_NAME VARCHAR(30)
)
INSERT INTO #names
SELECT 'Doe','John Jacob' UNION ALL
SELECT 'Smith','Joseph M' UNION ALL
SELECT 'Roberts','David'
SELECT
last_name,
first_name1 = SUBSTRING(first_name,1,CHARINDEX(' ',CAST(first_name AS CHAR(30)),1)),
first_name2 = ISNULL(SUBSTRING(first_name,CHARINDEX(' ',CAST(first_name AS CHAR(30)),1)+1,30),'')
FROM #names
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
July 20, 2009 at 7:33 am
thanks for that, i have been out of the office the last week so have not tried this, but its probably the best way to handle this.
i am sure the odd name will go astray, and i am not in control with how the payroll staff enter these names.
thanks again and I will get back to you with a result.
trout
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply