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

x
?
Solved

Searchinh name 'Fullname'

Posted on 2010-01-06
6
Medium Priority
?
292 Views
Last Modified: 2012-05-08
I have this stored procedure that can search for either first name or last name.
However,'I would like to modify it so it can also search if the fullname is entered - that is first and Last name or last and first name- for example: Joe Smith or Smith, Joe.
Is that possible? Maybe there is a way to do it but a lot of work involved.
 thanks
See below..
CREATE PROCEDURE sp_GetPersonName
@pname    varchar(25),
@typename    char(2)    
AS
if @typename = 'FN'
BEGIN
select  *
from VW_PERSON
where UPPER(RTRIM(FIRSTNAME)) like (@pname + '%')
order by LASTNAME, FIRSTNAME
END
ELSE
BEGIN
select  *
from VW_PERSON
where UPPER(RTRIM(LASTNAME)) like (@pname + '%')
order by LASTNAME, FIRSTNAME
END
GO
0
Comment
Question by:zachvaldez
  • 3
  • 2
6 Comments
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 400 total points
ID: 26194043

CREATE PROCEDURE sp_GetPersonName
@pname    varchar(25),
@typename    char(2)    
AS
if @typename = 'FN'
BEGIN
select  *
from VW_PERSON
where UPPER(RTRIM(FIRSTNAME)) like (@pname + '%')
order by LASTNAME, FIRSTNAME
END
ELSE IF @typename = 'LN'
BEGIN
select  *
from VW_PERSON
where UPPER(RTRIM(LASTNAME)) like (@pname + '%')
order by LASTNAME, FIRSTNAME
END
ELSE --fullname

select  *
from VW_PERSON
where isnull(lastName,'')+isnull(FirstName,'') = @pname
 or isnull(FirstName,'')+isnull(lastName,'') = @pname


GO
0
 

Author Comment

by:zachvaldez
ID: 26194695
It's not returning anything...
0
 
LVL 41

Accepted Solution

by:
Sharath earned 1200 total points
ID: 26195026
try this
CREATE PROCEDURE sp_GetPersonName
@pname    varchar(25),
@typename    char(2)    
AS
if @typename = 'FN' 
BEGIN
select  *
from VW_PERSON
where UPPER(RTRIM(FIRSTNAME)) like (@pname + '%')
order by LASTNAME, FIRSTNAME
END
ELSE IF @typename = 'LN' 
BEGIN
select  *
from VW_PERSON
where UPPER(RTRIM(LASTNAME)) like (@pname + '%') 
order by LASTNAME, FIRSTNAME
END
ELSE --fullname 

select  *
from VW_PERSON
where UPPER(RTRIM(FIRSTNAME)) like (@pname + '%') 
 or UPPER(RTRIM(LASTNAME)) like (@pname + '%') 
go

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 1200 total points
ID: 26195082
hmmm.... included a blank space between first and last names and added trim functions in Anessh's query.

How are you passing pname to SP in case of full name.
CREATE PROCEDURE sp_GetPersonName
@pname    varchar(25),
@typename    char(2)    
AS
if @typename = 'FN' 
BEGIN
select  *
from VW_PERSON
where UPPER(RTRIM(FIRSTNAME)) like (@pname + '%')
order by LASTNAME, FIRSTNAME
END
ELSE IF @typename = 'LN' 
BEGIN
select  *
from VW_PERSON
where UPPER(RTRIM(LASTNAME)) like (@pname + '%') 
order by LASTNAME, FIRSTNAME
END
ELSE --fullname 

select  *
from VW_PERSON
where ltrim(rtrim(isnull(lastName,'')+' '+isnull(FirstName,''))) = @pname 
 or ltrim(rtrim(isnull(FirstName,'')+' '+isnull(lastName,''))) = @pname 
GO

Open in new window

0
 

Author Comment

by:zachvaldez
ID: 26201999
It worked! But in reality, user may not completely put in the whole name if unsure so he may enter..
Joe Smi which may be Joe Smith or Joe Smithe..
so how can it take that requirement. would using Like instead of = will do it?

thanks
0
 
LVL 41

Expert Comment

by:Sharath
ID: 26203498
try like this.
CREATE PROCEDURE sp_GetPersonName
@pname    varchar(25),
@typename    char(2)    
AS
if @typename = 'FN' 
BEGIN
select  *
from VW_PERSON
where UPPER(RTRIM(FIRSTNAME)) like (@pname + '%')
order by LASTNAME, FIRSTNAME
END
ELSE IF @typename = 'LN' 
BEGIN
select  *
from VW_PERSON
where UPPER(RTRIM(LASTNAME)) like (@pname + '%') 
order by LASTNAME, FIRSTNAME
END
ELSE --fullname 

select  *
from VW_PERSON
where isnull(FirstName,'')+ ' '+isnull(LastName,'') like isnull(replace(@pName,' ','%'),'')+'%'
    or isnull(LastName,'')+ ' '+isnull(FirstName,'') like isnull(replace(@pName,' ','%'),'')+'%'
GO

Open in new window

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question