SQLTeam.com | Weblogs | Forums

Problem search keyword using like

tsql

#1

Hi all,
I have problem as : I use word "like" in sql to search such as
SELECT * FROM Customers WHERE address LIKE @Keyword;

if keyword is phrase word not only single word problem will occur !
For example : Keyword is "Ciputra E4" and i can not find records with address "18.03 E4 Ciputra "

How can i solve this problem ?
Give me a advice ! Thank you so much !


#2

SELECT * FROM Customers WHERE address LIKE '%' + @Keyword '%';


#3

Hi jamesK,

Thank you for your reply !
But "Ciputra E4" is phrase word so that all records which included "18.03 E4 Ciputra " can not match !
Mean that i would like to search full text ! every records include keyword "ciputra","e4" will be matched !
Do you give me other solution ? Thank you so much !


#4

If you need different part of the parameter to be their own search, you'll need to parse the parameter. Something like the following should work.

DECLARE @SearchItemList VARCHAR(8000) = '18.03, E4, Ciputra';

IF OBJECT_ID('tempdb..#SearchListItem', 'U') IS NOT NULL 
DROP TABLE #SearchListItem;

SELECT 
	SearchItem = RTRIM(sc.Item)
	INTO #SearchListItem
FROM 
	dbo.SplitCSVToTable8K(@SearchItemList, ',') sc;

-- see how the @SearchItemList value is split based on the parameter...
-- SELECT * FROM #SearchListItem sli;

SELECT 
	c.*
FROM 
	dbo.Customers c
WHERE 
	EXISTS (SELECT 1 FROM #SearchListItem sli WHERE c.Address LIKE '%' + sli.SearchItem + '%');