kllow kllow - 5 months ago 8
SQL Question

user input and database include '%' and using LIKE '%xx%' to search for the input in the database

SELECT
BrokerData.BrokerName,
Product.BAID,
Product.ProductName,
Product.ProductName,
Product.Type,
Product.RevenueFactor,
Product.VolumeFactor,
Product.PriceFactor
FROM Product
INNER JOIN BrokerData
ON Product.BAID = BrokerData.BAID
WHERE (ProductName Like '%xxx%' OR BaseProductName Like '%xxx%');


This query will help the users search for the product that contains "xxx". However, the database includes % in some of the product name, hence, when users input is 1%, it will give the product that contains "1".

I've tried to use CONTAINS, but it doesn't help for this.
The follwing is the query that I used CONTAINS but it doesn't work also:

SELECT
BrokerData.BrokerName,
Product.BAID,
Product.ProductName,
Product.ProductName,
Product.Type,
Product.RevenueFactor,
Product.VolumeFactor,
Product.PriceFactor
FROM Product
INNER JOIN BrokerData
ON Product.BAID = BrokerData.BAID
WHERE CONTAINS ((ProductName Like '%xxx%')
OR CONTAINS (BaseProductName Like '%xxx%'));

Answer

You can do this. Replace your user input with % to \%

Example Demo Code:

CREATE TABLE data_with_percent(
data VARCHAR(255)
)

INSERT INTO data_with_percent VALUES('1%');
INSERT INTO data_with_percent VALUES('2%');
INSERT INTO data_with_percent VALUES('1');

SELECT * FROM data_with_percent WHERE data LIKE CONCAT('%',REPLACE('1%', '%', '\%'),'%');

Testing as following: SQL Fiddle

Thank you.

Comments