Home » SQL For Programmers » SQL For Programmers – Finding a String

SQL For Programmers – Finding a String

tp_vol3_025 Many times in our queries, we aren’t looking for an exact match.  We are looking for one string that exists in another.  There are a couple statements available to us that will allow us to do this.

The first of these is the LIKE statement, and if you are familiar with DOS or the Linux equivalent, this should look familiar to you.

The most common usage of LIKE looks like this:

SELECT * FROM someTable
WHERE fieldName LIKE '%stringHere%'

Note the % inside the single quote marks.

This statement is telling SQL to find all of the rows from someTable where the contents of fieldName has the string ‘stringHere’ in it.

You could also do something like:

SELECT * FROM someTable
WHERE fieldName LIKE 'stringHere%'

which would find all the rows where fieldName started with ‘stringHere’

You can also use the following pattern matching characters:

SymbolDescriptions
_Look for any single character
[]Look for any single character listed in the set
fieldName LIKE ‘[abc]%’
Looks for anything that starts with a, b, or c.
[^]Look for any single character not listed in the set
fieldName LIKE ‘[^abc]%’
Looks for anything that doesn’t start with a, b or c.

If you need to find a string that includes one of these symbols, you’ll need to ESCAPE it and you’ll need to define the escape sequence.

So to look for a % in the middle of the string you would use:

SELECT * FROM someTable
WHERE fieldName LIKE '%%stringHere%'
ESCAPE '%'

This will look for all rows where the fieldName starts with the string ‘%stringHere’ because we’ve told it that % is the escape character and we’ve used %% twice in our search string.

A few other posts about SQL that might help you:

LIKE (Matching to a Pattern), Part 3 of 3

SQL Tutorial

 

Other post in SQL For Programmers

Related Post

  • SQL WHILE – SQL For ProgrammersSQL WHILE – SQL For Programmers The IF statement we looked at on Tuesday was pretty tame compared to the WHILE construct.Actually, the main thing you need to keep in mind is that WHILE is all you have.  There is no […]
  • SQL for .NET Programmers–The BookSQL for .NET Programmers–The Book Today, I finally announce the availability of my new book.  SQL For .NET Programmers.This book is based on the series of post I wrote in 2008.  If those post have been valuable to […]
  • Computed Columns Using LINQ to SQL ClassesComputed Columns Using LINQ to SQL Classes Last week we looked at the extension points Microsoft has wired into the LINQ to SQL classes and how they can be used to achieve some of the capabilities of the Business Logic Layer (BLL) […]
  • Storing a DataRow into a Session (server) variableStoring a DataRow into a Session (server) variable I recently ran into a situation where I needed to store a DataRow object, which is not serializable, into a Session variable using the Session Server.  As I mentioned yesterday, all […]
  • Windows Forms – Passing Parameters at RuntimeWindows Forms – Passing Parameters at Runtime I received the following question over the weekend: I've made a C# form application and I need to send a report name at runtime. How do I add an incoming parameter to the command line? […]

About Dave Bush

Dave Bush is a Full Stack ASP.NET developer focusing on ASP.NET, C#, Node.js, JavaScript, HTML, CSS, BootStrap, and Angular.JS.Does your team need additional help in any of the above? Contact Dave today.

One Pingback/Trackback