select - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

select

Author

Topic

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2005-07-19 : 04:06:33

Hi,
This select query works for searching for firstname and lastname or both first and lastname.
As you can see I look for a space so that it separates the first name from the last name.
But the query does not work if one person has two parts in his first name ie. kum cheong Donald
If kum is entered then the data is returned, but if kum cheong is entered nothing is returned.
Thanks
if (charindex(' ', ltrim(rtrim(@Name)))) > 0
begin
select @Firstname = left(ltrim(rtrim(@Name)), charindex(' ', ltrim(rtrim(@Name))))
select @Lastname = substring(ltrim(rtrim(@Name)), charindex(' ', ltrim(rtrim(@Name)))+1, len(ltrim(rtrim(@Name))))

select
p.PhoneBookID,
p.Firstname,
p.Lastname,
d.fullname as 'Department',
p.Phone1,
l.Description as 'Location'
from
tblPhoneBook as p left join tblDepartments as d on p.DepartmentID = d.DepartmentID
left join tblLocations as l on p.LocationID = l.LocationID
where
(p.Firstname like '%' + ltrim(rtrim(@name)) + '%'
OR p.Firstname like ltrim(rtrim(@name)) + '%'
OR p.Firstname like '%' + ltrim(rtrim(@name))
OR p.Firstname = ltrim(rtrim(@name))
OR p.Firstname like '%' + @Firstname + '%'
OR p.Firstname like @Firstname + '%'
OR p.Firstname like '%' + @Firstname
OR p.Firstname = @Firstname)
AND
(p.Lastname like '%' + ltrim(rtrim(@name)) + '%'
OR p.Lastname like ltrim(rtrim(@name)) + '%'
OR p.Lastname like '%' + ltrim(rtrim(@name))
OR p.Lastname = ltrim(rtrim(@name))
OR p.Lastname like '%' + @LastName + '%'
OR p.Lastname like @LastName + '%'
OR p.Lastname like '%' + @LastName
OR p.Lastname = @LastName)
order by
p.Firstname,
p.Lastname
end

else
begin
select
p.PhoneBookID,
p.Firstname,
p.Lastname,
d.fullname as 'Department',
p.Phone1,
l.Description as 'Location'
from
tblPhoneBook as p left join tblDepartments as d on p.DepartmentID = d.DepartmentID
left join tblLocations as l on p.LocationID = l.LocationID
where
p.Firstname like '%' + ltrim(rtrim(@name)) + '%'
OR p.Lastname like '%' + ltrim(rtrim(@name)) + '%'
order by
p.Firstname,
p.Lastname
end

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-07-19 : 04:30:00

Does this help


DECLARE @Name varchar(20), @FirstName varchar(20), @LastName varchar(20)
SET @Name = 'kum cheong Donald'
--SET @Name = 'Mickey Mouse'
SET @Name = REPLACE(@Name,' ','.')

SET @LastName = PARSENAME(@Name,1)
SET @FirstName = CASE WHEN PARSENAME(@Name,3) IS NULL THEN PARSENAME(@Name,2) ELSE PARSENAME(@Name,3) + ' ' + PARSENAME(@Name,2) END

SELECT @FirstName AS FirstName, @LastName AS LastName

Andy

Edit - Removed invalid REPLACE on SET @LastName

Beauty is in the eyes of the beerholder

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2005-07-19 : 04:36:12

If you only use the name 'lisa' it returns lastname as lisa and nothing for the firstname, whereas it should be other way round.
Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-19 : 04:47:58

Slight modification of Adny's code



DECLARE @Name varchar(20), @FirstName varchar(20), @LastName varchar(20)
SET @Name = 'kum cheong Donald'
--SET @Name = 'Mickey Mouse'
if charindex(' ',@name,1)>0 
Begin
SET @Name = REPLACE(@Name,' ','.')

SET @LastName = PARSENAME(REPLACE(@Name,' ','.'),1)
SET @FirstName = CASE WHEN PARSENAME(@Name,3) IS NULL THEN PARSENAME(@Name,2) ELSE PARSENAME(@Name,3) + ' ' + PARSENAME(@Name,2) END

SELECT @FirstName AS FirstName, @LastName AS LastName
end
else
Select @name

Madhivanan

Failing to plan is Planning to fail

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-07-19 : 04:50:06

Oh right
Just modify the query to handle that scenario then

Andy

Beauty is in the eyes of the beerholder

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2005-07-19 : 09:56:14

quote:

Originally posted by madhivanan

Slight modification of Adny's code



DECLARE @Name varchar(20), @FirstName varchar(20), @LastName varchar(20)
SET @Name = 'kum cheong Donald'
--SET @Name = 'Mickey Mouse'
if charindex(' ',@name,1)>0 
Begin
SET @Name = REPLACE(@Name,' ','.')

SET @LastName = PARSENAME(REPLACE(@Name,' ','.'),1)
SET @FirstName = CASE WHEN PARSENAME(@Name,3) IS NULL THEN PARSENAME(@Name,2) ELSE PARSENAME(@Name,3) + ' ' + PARSENAME(@Name,2) END

SELECT @FirstName AS FirstName, @LastName AS LastName
end
else
Select @name

Madhivanan

Failing to plan is Planning to fail

Hi,
I have modified my sql as you suggested.
It still does not return data if the firstname has two sections (i.e. Mark Richard Jones). Notice that 'Mark Richard' is the first name.
Here is my sql. Thanks
------------------
DECLARE @Name varchar(20), @FirstName varchar(20), @LastName varchar(20)
SET @Name = 'meeting room'

if charindex(' ',ltrim(rtrim(@name)),1)>0
Begin
SET @Name = REPLACE(@Name,' ','.')

SET @LastName = PARSENAME(REPLACE(@Name,' ','.'),1)
SET @FirstName = CASE
WHEN PARSENAME(@Name,3) IS NULL THEN PARSENAME(@Name,2)
ELSE
PARSENAME(@Name,3) + ' ' + PARSENAME(@Name,2)
END

--SELECT @FirstName AS FirstName, @LastName AS LastName

select
p.PhoneBookID,
p.Firstname,
p.Lastname,
d.fullname as 'Department',
p.Phone1,
l.Description as 'Location'
from
tblPhoneBook as p left join tblDepartments as d on p.DepartmentID = d.DepartmentID
left join tblLocations as l on p.LocationID = l.LocationID
where
(
p.Firstname like '%' + ltrim(rtrim(@FirstName)) + '%'
OR p.Firstname like ltrim(rtrim(@FirstName)) + '%'
OR p.Firstname like '%' + ltrim(rtrim(@FirstName))
OR p.Firstname = ltrim(rtrim(@FirstName))
)

AND
(
p.Lastname like '%' + ltrim(rtrim(@LastName)) + '%'
OR p.Lastname like ltrim(rtrim(@LastName)) + '%'
OR p.Lastname like '%' + ltrim(rtrim(@LastName))
OR p.Lastname = ltrim(rtrim(@LastName))
)
order by
p.Firstname,
p.Lastname

end
else
--Select @name

select
p.PhoneBookID,
p.Firstname,
p.Lastname,
d.fullname as 'Department',
p.Phone1,
l.Description as 'Location'
from
tblPhoneBook as p left join tblDepartments as d on p.DepartmentID = d.DepartmentID
left join tblLocations as l on p.LocationID = l.LocationID
where
p.Firstname like '%' + ltrim(rtrim(@name)) + '%'
OR p.Lastname like '%' + ltrim(rtrim(@name)) + '%'
order by
p.Firstname,
p.Lastname

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-19 : 10:02:25

What did you get as first name?
I think it should work

Madhivanan

Failing to plan is Planning to fail

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2005-07-19 : 10:13:26

it correctly returns 'Mark Richard' as the firstname
But if you enter 'mark richard' it returns mark as firstname and richard as last name whereas it should be 'mark richard'. The thing is that the system should know that it can easily be 'mark' as firstname and 'richard' as lastname
Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-19 : 10:24:24

Then you have define how you seperate names as first and last
If the name has less than three parts('Mark' or 'Mark Richard'), should it be first name?

Madhivanan

Failing to plan is Planning to fail

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-07-19 : 10:32:47

Redesign your application/sproc to send/accept 2 paramaters - 1st & last names!

How would SQL server know that 'Who Me' is just a 1st name or its the 1st AND last name, as Madhivanan said you need to define your "logic"

Andy

Beauty is in the eyes of the beerholder

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2005-07-19 : 10:46:19

I think this is asking for too much on sql to do.
'mark richards' can be a firstname and a lastname OR just the firstname (with two parts).
Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-20 : 01:05:37

Post the exact table structures

Madhivanan

Failing to plan is Planning to fail

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2005-07-20 : 03:48:17

is a small table.
PhoneBookID int primary key
firstname varchar(50)
lastname varchar(50)
Phone1 varchar(50)

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-20 : 04:10:26

Cant you use simply like this without splitting?

(Firstname like '%' + ltrim(rtrim(@name)) + '%' OR
Lastname like '%' + ltrim(rtrim(@name)) + '%')

Madhivanan

Failing to plan is Planning to fail

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2005-07-20 : 04:19:00

this simple where clause will not work because if the name 'james brown' is passed it does not get returned since you are doing a like %...%
Looking at my previous posts you will see that I have used something similar but in a greater depth and does not return what I initially raised my query.
Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-20 : 04:50:36

You have to modify your sp to accept first name and last name seperately as you have no proper way of identifying the names

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources