How to use SQL Charindex () function to search for strings – WP Newsify

Working with strings in SQL can feel that they are looking for a missing sock. Sometimes you just have to find where a word or desire in a longer piece of text starts. That’s where the Charindex () Function comes to the rescue!

This powerful small tool helps you find the position of one string in another. It is as an assignment “found” when you are looking for treasures 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 are looking for.
• chord – The text you are looking for.
• Start_ location – Optional. Where to start searching.

Why use it?

If you need:

• Check if there is a word in a string.
• Know where it starts.
• Cut out something based on position.

Charindex () Has your back!

Simple example

Let’s say you have this name: ‘Johnathon Smith’. Let’s now find where ‘Smith’ starts.

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

The result will be: 11

That’s because ‘Smith’ starts with the 11^one character.

What if the string is not found?

If the substring does not exist, Charindex () return 0.

Example:

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

The result will be here 0Because ‘Davis’ is not part of the name.

Using the optional starting location

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

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

This tells SQL to search for ‘A’ of the 3^RD character. It thinks it’s in position 4.

Pretty smart, right?

Real-World Example: e-mails

Let’s say you work with e -mails:

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

This returns 6 Because it is ‘@’ symbol it is 6^one character.

You can then use this information to split the username or domain with the help of string functions such as Left () or RIGHT().

Combining Charindex () with other functions

Charindex () Works great in itself, but combining with other string functions makes it even more powerful!

Example: Take the username for the ‘@’

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

You get: buddy

We deducted 1 to prevent the ‘@’ from being recorded.

Find words in a sentence

Let’s look for a word in a sentence:

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

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

Easy, right?

Case sensitivity

Charindex is not-sensitive Standard. So:

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

This still returns a number such as ‘SQL’ or ‘SQL’ is found.

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

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

Now it only corresponds to ‘SQL’ in small letters.

Use Charindex where Clause

You can filter your data with Charindex in a WHERE clause.

Example:

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

This is what all users with a Gmail address.

Pro -tips

• Remember: if Charindex does not find your text, it will return 0 – not zero.
• You can use it to search several times by adjusting the starting position.
• Use with Substring () And S () To cut and fill in your text.

A nice use case

Let’s say you have product codes such as: ‘Prod-2347’ or ‘Item-7832’

And you want to extract everything after the dashboard.

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

This only brings you the numbers: ‘2347’” ‘7832’and so forth.

Watch out for …

• Start positions that are too high – you may miss your competition.
• Try to calculate positions when Charindex 0 returns – that can break math!
• White space or invisible signs – they can mess your search.

Exercise time!

Try to perform this yourself:

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

What did you get? (Hint: Tel Signs carefully!)

Following:

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

That should return … yes, ‘cat’!

Last thoughts

Charindex () Is easy to use, but still super handy. Think of it as your string-sniffing robot. Whether you find domains, names separate or record codes, it is an essential tool in your SQL toolbox.

So go ahead – go down those strings like a professional!

Latest posts by editorial staff (see everything)