I have Vehicle_parts table like below,

I need to do keywords search on entire column of the table.

Example: If the Keywords: '11111' & '88888' then the result will be like below table,

I tried Like statement in where clause but the ** where** clause not allowing to search multiple columns.

Also I tried Functions like below,

Create function [Keyword_Search](@Keyword Varchar(100))

Returns table

as

RETURN(SELECT * FROM Vehicle_parts WHERE 
      [Vehicle_Major_Part_Number] LIKE @Keyword
      or [Vehicle_Sub_Part_Number] LIKE @Keyword

SELECT * FROM Keyword_Search ('111%')

Here, I could not provide multiple keywords at a time for searching.

Kindly suggest me to the efficient way for searching the multiple keywords on all columns present in the table.

CodePudding user response：

You can put multiple LIKE conditions in the WHERE clause that check every condition you named. According to your description, this query will work:

SELECT vehicle_id, vehicle_major_part_number, vehicle_sub_part_number
FROM vehicle_parts
WHERE vehicle_major_part_number LIKE '11111%'
OR vehicle_major_part_number LIKE '88888%'
OR vehicle_sub_part_number LIKE '11111%'
OR vehicle_sub_part_number LIKE '88888%';

Queries with LIKE and especially with % (to mark further characters might follow) are often slow and as you see, they can be long and bad to read, too. You could also try something like this:

SELECT vehicle_id, vehicle_major_part_number, vehicle_sub_part_number
FROM vehicle_parts
WHERE CONCAT(vehicle_major_part_number, vehicle_sub_part_number) LIKE '111%' OR 
CONCAT(vehicle_major_part_number, vehicle_sub_part_number) LIKE '