从数据库搜索产品 [英] search prodcuts from database

问题描述

我要为此创建一个存储过程，我有一个表格产品.我想按产品名称搜索产品.如果我输入"PEN DRIVE"之类的产品名称，则先显示有关"PEN DRIVE"的结果，然后显示"PEN"和"DRIVE".

I want to create a stored procedure for this I have a table products.I want to search products by product name. If i enter a product name like "PEN DRIVE" firstly show result about "PEN DRIVE" after that "PEN" and "DRIVE".

推荐答案

所有您需要要做的是中断空间上的输入，并生成一个临时表来保存结果.然后，您在临时表中选择与
相匹配的每一行

All you need to do is to break the input on the space, and generate a temporary table to hold the results. You then select into the temporary table each row which matches

productName LIKE '%' + fragment + '%'

使用这种情况，它将帮助您创建存储过程.

use this scenario, it will help you to create your store procedure.

DECLARE @PRODUCT TABLE(
	ID INT IDENTITY(1,1)
	,PRODUCTCODE VARCHAR(MAX)
	,PRODUCTNAME VARCHAR(MAX)
	,PRODUCTPRIZE VARCHAR(MAX)
)


INSERT INTO @PRODUCT VALUES ('P001','PEN DRIVE','100')
INSERT INTO @PRODUCT VALUES ('P002','PEN DRIVE','110')
INSERT INTO @PRODUCT VALUES ('P003','PEN DRIVE','120')
INSERT INTO @PRODUCT VALUES ('P004','PEN','10')
INSERT INTO @PRODUCT VALUES ('P005','PEN','20')
INSERT INTO @PRODUCT VALUES ('P006','DRIVE','1000')
INSERT INTO @PRODUCT VALUES ('P007','LIGHT','200')
INSERT INTO @PRODUCT VALUES ('P008','MONITOR','10000')

DECLARE @SEARCHSTRING VARCHAR(MAX)
DECLARE @FIRESTWORD VARCHAR(MAX)
DECLARE @SECONDWORD VARCHAR(MAX)
SET @SEARCHSTRING = 'PEN DRIVE'

SELECT @FIRESTWORD = RTRIM(LTRIM(SUBSTRING(@SEARCHSTRING, 1,CHARINDEX(' ', @SEARCHSTRING)))),
@SECONDWORD = RTRIM(LTRIM(SUBSTRING(@SEARCHSTRING, CHARINDEX(' ', @SEARCHSTRING),LEN(@SEARCHSTRING)))) 

SELECT * FROM @PRODUCT WHERE PRODUCTNAME = @SEARCHSTRING OR PRODUCTNAME = @FIRESTWORD OR PRODUCTNAME = @SECONDWORD

结果会像这样...

Result will be like this...

ID          PRODUCTCODE PRODUCTNAME PRODUCTPRIZE
----------- ----------- ----------- ------------
1           P001        PEN DRIVE   100
2           P002        PEN DRIVE   110
3           P003        PEN DRIVE   120
4           P004        PEN         10
5           P005        PEN         20
6           P006        DRIVE       1000

这篇关于从数据库搜索产品的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！