SQl: Get the matching data for firstname lastname,Mobile

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Hi ALl,

I am facing the issue in framing the logic for writing the query.
There are three textboes like FName,Lname,Mobno if user enters any one data and click search the data matching firstname should come. Similarly if he inputs Fname and lname and click serach it should result matching Fname and Lname together.
ACtually the table is as below
Fname |Lname |mobileNo
prashant|P|888
prashant| s|1234
What I want if they GIVE fname=prahant and search for it both the rows should come but if they give Fname=Prashan and lname=p only first row should come.

I am unable to achieve this after spending so many hours. Can some one pls advise me the logic
Regards
Prashant

Posted 28-Nov-14 0:20am

Prashant Bangaluru

Updated 28-Nov-14 21:17pm

v3

Add a Solution

Comments

Tomas Takac 28-Nov-14 6:23am

Can you show us what you have so far?

Shweta N Mishra 28-Nov-14 6:36am

does your data field in table contains both firstname and lastname in same column ?

Prashant Bangaluru 29-Nov-14 3:17am

SOrry for the late reply ..I just updated the table structure actually they are 2 different coumns.

2 solutions

Solution 2

Hi,

Your can try like this...

SQL

SELECT your-column-list
FROM
your-table
WHERE
FName+Lname+Mobno like '%your textbox values%'

Suppose you have space between all three fields..

then your put space between them like .

SQL

SELECT your-column-list
FROM
your-table
WHERE
FName+' '+Lname+' '+Mobno like '%your textbox values%'

Posted 28-Nov-14 2:17am

Senthilnathan Vaithi

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sinisa Hajnal · Accepted Answer · 2014-11-28T00:39:00

Create a stored procedure (or query) with all three parameters and use it like this:

SQL

@FName varchar(100)
@LName varchar(100)
@Mobno varchar(20)

SET @FName = '%' + ISNULL(LTRIM(RTRIM(@FName)), '') + CASE WHEN @FName IS NULL THEN '' ELSE '%' END

-- repeat the above for other two variables
-- what you're doing is: set your variable to '%word%' which will enable to look for partial information or just % (that is, generic look for everything)

SELECT your-columns-list
FROM
    your-table
WHERE
    FName LIKE @Fname
AND
    LName LIKE @Lname
AND
    Mobno LIKE @Mobno

If you have to have exact match you can somewhat simplify

SQL

SET @FName = NULLIF(LTRIM(RTRIM(@Fname)), '')
-- repeat for other two
-- this ensures that your variable is null if it is empty string

SELECT your-column-list
FROM
your-table
WHERE
(FName = @Fname OR @Fname IS NULL)
AND 
(LName = @LNAme OR @LName IS NULL)
AND
(Mobno = @Mobno OR @Mobno IS NULL)

Finally, you could just do dynamic SQL, using only the variable you have in WHERE clause and calling sp_execute[^] to get the result.

If this helps please take time to accept the solution. Thank you.