Searching a field - 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 2005 Forums

Transact-SQL (2005)

Searching a field

Author

Topic

lols
Posting Yak Master

174 Posts

Posted - 2007-11-12 : 06:31:37

Hi,

I have a table employee with firstname and lastname columns.

I have written an exact search query which matches the data entered by the user :

CREATE PROCEDURE SearchEmployee
@fnm nvarchar(20) ,@lnm nvarchar(22) = null,
AS
select employeeid, employeecity from Employees
WHERE firstname = @fnm
AND lastname = @lnm

The problem is sometimes the user doesnt give lastname or at times, adds up both the first and last nm together. So the search doesnt work.

I was thinking can I write a statement like this :

select employeeid, employeecity from Employees
WHERE name = @fnm + @lnm

How do I combine columns?

thank you

nduggan23
Starting Member

42 Posts

Posted - 2007-11-12 : 06:34:01

Just use an OR instead of AND. will do a combination of first and last name.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-12 : 06:36:59

According to your own specs

quote:
Originally posted by lols

I have written an exact search query which matches the data entered by the user

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-12 : 06:37:51

Something like this:

select employeeid, employeecity from Employees
WHERE name = @fnm + coalesce(' ' + @lnm, '')

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

nduggan23
Starting Member

42 Posts

Posted - 2007-11-12 : 06:44:56

Sorry miss read your post OP.

where name = (@frm + ' ' + @lnm)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-12 : 06:55:04

quote:
Originally posted by nduggan23

Sorry miss read your post OP.

where name = (@frm + ' ' + @lnm)

That won't work when @lnm is Null.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

nduggan23
Starting Member

42 Posts

Posted - 2007-11-12 : 06:59:02

Can you explain why please?

or does it require ISNULL('',@lnm) to work?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-12 : 07:01:31

quote:
Originally posted by nduggan23

Can you explain why please?

or does it require ISNULL('',@lnm) to work?

Select 'a'+NULL

Madhivanan

Failing to plan is Planning to fail

lols
Posting Yak Master

174 Posts

Posted - 2007-11-12 : 07:06:26

thanks for all your replies..

I do not have a column called 'name'..the only columns are firstname and lastname

How can i make this query work with the given columns?

select employeeid, employeecity from Employees
WHERE name = @fnm + coalesce(' ' + @lnm, '')

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-11-12 : 07:11:23

select employeeid, employeecity from Employees
WHERE (firstname = @fnm or @fnm is null)
AND (lastname = @lnm or @lnm is null)

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-12 : 07:13:58

select employeeid, employeecity from Employees
WHERE firstname = @fnm
OR lastname = @lnm

Madhivanan

Failing to plan is Planning to fail

lols
Posting Yak Master

174 Posts

Posted - 2007-11-12 : 07:24:34

thanks again..

It seems I have not being able to describe my problem correctly. I apologize. Let me try again :

So let us assume i searched for the name 'John Matthew'. Now the same string can come in 2 forms..

1.
@fnm = 'John'
@lnm = 'Matthew'

2.
@fnm ='John Matthew'
@lnm = ''

Using the 'OR' will search all results with last name as 'Matthew' which is not desired.

I want the overall effect to be that of an exact search with of course the above requirement in place i.e sometimes the user doesnt give lastname or at times, adds up both the first and last nm together

I tried this :

select employeeid, employeecity from Employees
WHERE firstname = @fnm + coalesce(' ' + @lnm, '') OR lastname = @lnm

How do I modify this?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-11-12 : 07:55:32

this would be one way...
WHERE firstname + coalesce(' ' + lastname , '') = @fnm + coalesce(' ' + @lnm, '')

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-12 : 08:04:54

select employeeid, employeecity from Employees
WHERE firstname LIKE '%' + @fnm + '%'
OR lastname LIKE '%' + @lnm + '%'

E 12°55'05.25"
N 56°04'39.16"

lols
Posting Yak Master

174 Posts

Posted - 2007-11-12 : 08:31:03

Hi..will LIKE yield exact search..?

I want exact search..i will try spirit's suggestion.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources