WP Newsify

How to Use SQL CHARINDEX() Function to Search Strings

Editorial Staff
PinLinkedIn

Working with strings in SQL can feel like hunting for a missing sock. Sometimes you just need to find where a word or phrase starts inside a longer piece of text. That’s where the CHARINDEX() function comes to the rescue!

This mighty little tool helps you locate the position of one string inside another. It’s like a “find” command when you’re searching for treasure in a sea of text.

What is CHARINDEX()?

CHARINDEX() is a built-in SQL function. It tells you the location (position) of a substring within a string.

The syntax is simple:

CHARINDEX(substring, string [, start_location])

Let’s break that down:

Why Use It?

If you need to:

CHARINDEX() has your back!

Simple Example

Let’s say you have this name: ‘Johnathon Smith’. Now let’s find where ‘Smith’ begins.

SELECT CHARINDEX('Smith', 'Johnathon Smith');

The result will be: 11

That’s because ‘Smith’ starts at the 11th character.

What if the String Isn’t Found?

If the substring doesn’t exist, CHARINDEX() returns 0.

Example:

SELECT CHARINDEX('Davis', 'Johnathon Smith');

The result here will be 0, because ‘Davis’ isn’t part of the name.

Using the Optional Start Location

Let’s say our string is ‘banana’. You want to find the second ‘a’.

SELECT CHARINDEX('a', 'banana', 3);

This tells SQL to start looking for ‘a’ from the 3rd character. It finds it at position 4.

Pretty clever, right?

Real-World Example: Emails

Let’s say you’re working with emails:

SELECT CHARINDEX('@', 'buddy@example.com');

This returns 6 because the ‘@’ symbol is the 6th character.

You could then use this info to split out the username or domain using string functions like LEFT() or RIGHT().

Combining CHARINDEX() with Other Functions

CHARINDEX() works great on its own, but pairing it with other string functions makes it even more powerful!

Example: Grab the username before the ‘@’


SELECT LEFT('buddy@example.com', CHARINDEX('@', 'buddy@example.com') - 1);

You’ll get: buddy

We subtracted 1 to avoid including the ‘@’.

Find Words in a Sentence

Let’s search for a word in a sentence:

SELECT CHARINDEX('SQL', 'I love learning SQL functions!');

This gives us 17 — ‘SQL’ starts at position 17.

Easy, isn’t it?

Case Sensitivity

CHARINDEX is not case-sensitive by default. So:


SELECT CHARINDEX('sql', 'I love SQL!');

This will still return a number if ‘sql’ or ‘SQL’ is found.

If you need a case-sensitive search, you can set a collation like this:


SELECT CHARINDEX('sql', 'I love SQL!' COLLATE Latin1_General_CS_AS);

Now it will only match ‘sql’ in lowercase.

Using CHARINDEX in WHERE Clause

You can filter your data using CHARINDEX inside a WHERE clause.

Example:


SELECT *
FROM users
WHERE CHARINDEX('gmail.com', email) > 0;

This finds all users with a Gmail address.

Pro Tips

A Fun Use Case

Let’s say you have product codes like: ‘PROD-2347’ or ‘ITEM-7832’

And you want to extract everything after the dash.


SELECT SUBSTRING(code, CHARINDEX('-', code) + 1, LEN(code))
FROM products;

This gets you just the numbers: ‘2347’, ‘7832’, and so on.

Watch Out For…

Practice Time!

Try running these yourself:

SELECT CHARINDEX('cat', 'The black cat sat on the mat');

What did you get? (Hint: Count characters carefully!)

Next:

SELECT SUBSTRING('The black cat sat on the mat', CHARINDEX('cat', 'The black cat sat on the mat'), 3);

That should return… yep, ‘cat’!

Final Thoughts

CHARINDEX() is easy to use, yet super useful. Think of it as your string-sniffing robot. Whether you’re finding domains, separating names, or slicing up codes, it’s an essential tool in your SQL toolbox.

So go ahead—hunt down those strings like a pro!

Follow Us
Latest posts by Editorial Staff (see all)
PinLinkedIn
Exit mobile version