SQl：获取firstname lastname，Mobile的匹配数据 [英] SQl: Get the matching data for firstname lastname,Mobile

问题描述

嗨ALl，



我在构建编写查询的逻辑方面遇到了问题。

有三个文本框，比如FName ，Lname，Mobno如果用户输入任何一个数据并单击搜索匹配名字的数据应该来。类似地，如果他输入Fname和lname并单击serach，它应该得到Fname和Lname匹配。

实际上表格如下

Fname | Lname | mobileNo

prashant | P | 888

prashant | s | 1234

我想要的是如果他们给fname = prahant并搜索它们两行都应该来，但是如果他们给Fname = Prashan而lname = p只有第一行应该来。



花了这么多时间我无法实现这一目标。有人可以告诉我逻辑

问候

Prashant

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

推荐答案

创建存储过程（或查询） ）使用所有三个参数并使用它如下：

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

@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

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)

最后，您可以只使用WHERE子句中的变量并调用sp_execute [ ^ ]获得结果。



如果这有帮助，请花时间接受解决方案。谢谢。

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.

您好，



您可以尝试这样...

Hi,

Your can try like this...

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 .

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

这篇关于SQl：获取firstname lastname，Mobile的匹配数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！