0

Suppose I have 3 records, named 'PROBLEM', 'BIG PROBLEM', and 'VERY BIG PROBLEM' in the column Name and I want to filter in the stored procedure. My SP can take one parameter, that is @p and I want find records on the basis of that parameter. I am stuck in the condition where I should find 'VERY BIG PROBLEM' if I get parameter either

  1. VE (As 'VERY' starts with 'VE')
  2. BI (As 'BIG' starts with 'BI')
  3. PRO (As PROBLEM start with 'PRO')

One solution could be using Where name like '%' + @p + '%', but it can give undesired results like if I search for 'Phil' and give parameter as 'Ph' then result will be 'Phil' and 'Joseph' as both has 'Ph' keyword. Is there any solution? As I am using ADO.Net, C# solution also be appreciated.

My Goal

If I search 'VE' then 'VERY BIG PROBLEM' should come as a result if I use name like @p + '%', but the same result should come on 'VE', 'BI' and 'PRO' also. Its like splitting the value on the white space and matching the result which each word.

I can give C# example:

string name = 'VERY BIG PROBLEM';
string[] terms = name.Split(' ');

Note the term array contains 3 values; that is 'VERY', 'BIG', and 'PROBLEM'.

So if I search for either 'VE', 'BI', or 'PRO' it should give me the same result. If it's still unclear, suppose a scenario where we want to search for the name of a cricketer 'Abraham Benjamin Devilliers'. Let my parameter be 'Abrah', 'Benj' or 'Dev'; it should search 'Abraham Benjamin Devilliers' for me.

pixelmeow
  • 654
  • 1
  • 9
  • 31
Imad
  • 7,126
  • 12
  • 55
  • 112
  • Not sure what you want on this Where @p like '%' + @p +%? Why would you use @p instead of Name since the records are stored in the Name column? – Hatjhie Jul 07 '14 at 03:26
  • Ok. Now, what does it mean by Where name like "%PH%", since the column name is only three, VE, BI, and PRO? Please share your objective. What do you want to achieve? – Hatjhie Jul 07 '14 at 03:58
  • 1
    If you want only to match begining letters then you must try somthing like @p + '%'. this might can help you. – Ankit Bajpai Jul 07 '14 at 04:35
  • Update question for being more descriptive. – Imad Jul 07 '14 at 05:40

1 Answers1

0

If you want to search like starting with you can use like @p+'%' as suggested by @ankit.

If you want c# solution which is not good as you will fetch more data and then filter out. You can use linq to filter data.

You can use StartsWith in linq to filter data
Example :-How to improve this LINQ query for search

Edit 1

You can combine two condition in or clouse

  (LIKE '%[^a-z]'+@p+'[^a-z]%') OR (LIKE @p +`%`)

References : Search for “whole word match” with SQL Server LIKE pattern

You can also use

[MyColumn] Like '% '+  @p +' %' 
OR [MyColumn] Like '% '+ @p 
OR [MyColumn] Like ' %' 
OR [MyColumn] =  @p

Edit: An alternate approach (only for ascii chars) could be: 

'#'+[MyColumn]+'#' like '%[^a-z0-9]'+ @p+'[^a-z0-9]%'

References Match only entire words with LIKE?

Community
  • 1
  • 1
शेखर
  • 17,412
  • 13
  • 61
  • 117
  • I have 3 words in name column, as in question. search should be performed on initial of either word. – Imad Jul 07 '14 at 05:32
  • I gave example with name of a cricketer in my updated question. Doesn't matter you search with first name, last name or middle name. It should give me result. – Imad Jul 07 '14 at 05:50