Search database for substring based on user input using SQL

Question

I am doing a uni project where I need to search for a user from a database in Visual Studio 2010, based on their FirstName, SurName, EmailAddress or SkillSet using a SQL command in vb.net.

So from the start... The use registers and includes a skill set that is a single textbox that they type in the likes of "Java", "C#", etc.

I can search for the everything fine, apart from the SkillSet if the user has more than one skill.

I want to search for anything using "LIKE" in the SQL command.

Here's what I have at the minute for searching for a user:

SQL command to select the details I want

Dim command As New SqlCommand("SELECT [FirstName], [SurName], [EmailAddress], [SkillSet], [UserID] FROM [Users] WHERE ([FirstName] = @FirstName OR [SurName] = @SurName OR [EmailAddress] = @EmailAddress OR [SkillSet] LIKE '%' @SkillSet '%')", connection)

Creating the Paramaters

    Dim firstnameParam As New SqlParameter("@FirstName", Me.UserSearchTextBox.Text)
    Dim surnameParam As New SqlParameter("@SurName", Me.UserSearchTextBox.Text)
    Dim emailParam As New SqlParameter("@EmailAddress", Me.UserSearchTextBox.Text)
    Dim skillSetParam As New SqlParameter("@SkillSet", Me.UserSearchTextBox.Text)

    command.Parameters.Add(firstnameParam)
    command.Parameters.Add(surnameParam)
    command.Parameters.Add(emailParam)
    command.Parameters.Add(skillSetParam)`

A. Gilfrin · Answer 1 · 2012-11-20T16:21:43.573

1

Rather than LIKE you would want to use IN

Dim command As New SqlCommand("SELECT [FirstName], [SurName], [EmailAddress], [SkillSet], [UserID] FROM [Users] WHERE ([FirstName] = @FirstName OR [SurName] = @SurName OR [EmailAddress] = @EmailAddress OR [SkillSet] IN (@SkillSet)", connection)

The SkillSet would need to be passed in a format such as

'SQL','PHP','JAVA'

I don't know Vb.net well enough to advise on formatting the user input in to the string needed.

edited Nov 20 '12 at 16:21

answered Nov 20 '12 at 16:15

A. Gilfrin

302
1
8

That does bring up the question of design - Do you have a relational table of skill sets per user? Then do a join and `IN` as you have suggested? And also what to expect - `like %c%` will return C++ and C# whilst `IN` would only return exact matches – RemarkLima Nov 20 '12 at 16:31
1

Very good point, would people have only a single skill or a list of skills represented as a string. In the first my solution would work, however it wouldn't be a good database design (depending on the circumstances). In the later RemarkLima's solution would work although you would need to take in to account his point about partial matches and again not the best design. Even if that were the case what if somebody had say "C#,PHP" but the search was done for "PHP,C#". – A. Gilfrin Nov 20 '12 at 17:03
Absolutely! Either answer would be a solution to the problem, but as you say, the design sounds like it could be improved upon. Probably setup a skills table, with a skill name to PersonID, then get a list of skills that match and RIGHT JOIN onto the person details - For example. – RemarkLima Nov 21 '12 at 08:42

score 0 · Answer 2 · edited May 23 '17 at 12:08

A very quick Google reveals the following answers on this very site:

Use of SqlParameter in SQL LIKE clause not working

Howto? Parameters and LIKE statement SQL

So quite simply you need to use & (as you're in VB.NET - This will be + in C#.NET) to concatenate the string '%' with the parameter, like so:

Dim command As New SqlCommand("SELECT [FirstName], [SurName], [EmailAddress], [SkillSet], [UserID] FROM [Users] WHERE ([FirstName] = @FirstName OR [SurName] = @SurName OR [EmailAddress] = @EmailAddress OR [SkillSet] LIKE '%' & @SkillSet & '%')", connection)

or

Dim skillSetParam As New SqlParameter("@SkillSet", "%" & Me.UserSearchTextBox.Text & "%")

And remove the '%' from the query would yield the same results.

score 0 · Accepted Answer · answered Nov 21 '12 at 11:50

Got it working now. I was missing another set of brackets. Now I can retrieve results if only a few characters are inserted by the user. Thanks for your help guys!!

Dim command As New SqlCommand("SELECT [FirstName], [SurName], [EmailAddress], [UserID], [SkillSet] FROM [Users] WHERE ([FirstName] = @FirstName OR [SurName] = @SurName OR [EmailAddress] = @EmailAddress OR [SkillSet] LIKE '%@SkillSet%' )", connection)

Search database for substring based on user input using SQL

3 Answers3