Can't make a partial match from TextBox in a query

Question

I have a column with data like this "Tom Hardy" and I add a new query to search in this column like this :

SELECT  Number, Name, Business, [Tax File No], Phone
FROM            Archive
WHERE        (Name = @Name)

and it works when I entered the full name like "Tom Hardy" but I want to find results by searching just "Tom" or "Hardy".

I also tried :

SELECT  Number, Name, Business, [Tax File No], Phone
FROM            Archive
WHERE        (Name IN (@Name))

Neither work.

Using Visual Studio 2010 with C# and SQL database connected to it.

Please help, thanks!

score 1 · Accepted Answer · edited May 23 '17 at 12:12

1

You need to add a wild card to your parameter

WHERE Name LIKE '%' + @Name + '%'

So

SELECT  Number, Name, Business, [Tax File No], Phone
FROM            Ahrcive
WHERE        (Name COLLATE UTF8_GENERAL_CI like '%' + @Name + '%')

I don't think you can do this using the 'in' keyword.

edited May 23 '17 at 12:12

Community

1
1

answered Nov 27 '12 at 12:04

Dave

8,163
11
67
103

Thank you for your concern but it didn't work. Still can't search a not full name – Medo Omar Nov 27 '12 at 12:32
1

@MohammedAhmed Updated again as an alternative to Jorge lower case suggestion (although it may be too localized for your needs). You can also add this to the database if needed http://stackoverflow.com/questions/2876789/case-insensitive-for-sql-like-wildcard-statement – Dave Nov 27 '12 at 12:45
+1 for elegance :) .. I don't know why Mohammed keeps having problems though – Jorge Alvarado Nov 27 '12 at 12:48
Thank you. @MohammedAhmed Are you getting any errors, or just 0 results? Can you try the code above in Sql Server Management Studio instead of in code so we can rule that out. – Dave Nov 27 '12 at 12:48
@Dave when trying to use your last update it gave me error "Invalid Collation UTF8_GENERAL_CI " – Medo Omar Nov 27 '12 at 12:51
@ Dave It worked now with you first post all I have to do is change the column type from nchar to nvarchar. Thank you very much – Medo Omar Nov 27 '12 at 12:57

score 1 · Answer 2 · answered Nov 27 '12 at 12:40

1

Dave is right, except that mybe you also want to check for lowercase/uppercase escenarios, maybe that is the reason you are not receiving good results

like :

SELECT  Number, Name, Business, [Tax File No], Phone
FROM            Archive
WHERE        (LOWER(Name) like '%' + LOWER(@Name) + '%')

this statement converts both values to lowercase and the comparison is valid again

answered Nov 27 '12 at 12:40

Jorge Alvarado

2,664
22
33

@MohammedAhmed - Can you please try this in SSMS (SQL Server Management Studio) so we can rule out any C# / connection / coding issues. – Dave Nov 27 '12 at 12:52

Can't make a partial match from TextBox in a query

2 Answers2