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:
- substring – The text you’re looking for.
- string – The text you’re searching inside.
- start_location – Optional. Where to start searching from.
Why Use It?
If you need to:
- Check if a word exists in a string.
- Know where it starts.
- Cut something out based on position.
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
- Remember: If CHARINDEX doesn’t find your text, it returns 0 — not NULL.
- You can use it to search multiple times by adjusting the start position.
- Use with SUBSTRING() and LEN() to slice and dice your text.
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…
- Start positions that are too high — you might miss your match.
- Trying to calculate positions when CHARINDEX returns 0 — that can break math!
- Whitespace or invisible characters — they can mess up your search.
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!
- How to Use SQL CHARINDEX() Function to Search Strings - September 6, 2025
- The Ultimate Guide to Diagramming Tools Like Visio for Mac - September 6, 2025
- Why Every Mac User Needs These Powerful Maintenance Tools - September 6, 2025