SQL Server - Charindex: A Different Function to Break Split Strings

Speak guys!
A little over 5 years after sharing with you the function code Split, which allows you to retrieve a part of the string broken by a delimiter, this time I come to share a new function, called charindexada, written by Brunno Araújo and who kindly gave me the “copyright rights” to share it here on the blog.

Interested in learning more about split?

• Breaking strings into substrings using separator (Split string)
• Table-valued function to break a string into lines up to N characters long
• How to wrap a string in a substring table using a delimiter in SQL Server

This function has the basic purpose of allowing some specialized queries using separators in a string.

Its parameters are:

• @delimiter_left: String representing the left delimiter of the string. You can use the same delimiter at the beginning and end or they can be different delimiters.
• @initial_position: Initial delimiter number. Specifies from which part of the string to start extracting text.
• @delimiter_right: String representing the delimiter to the right of the string. If different from the initial delimiter, you can extract text that is between the two delimiters. If equal to the initial delimiter, parts of the string (middle) can be extracted
• @position_fim: End delimiter number. Specifies how many parts will be extracted from the text.
• @string: The string itself that will be parsed and extracted
• @type: Indicates the return type of the function. Type = 0 returns the position of the localized string. Type = 1 (default), returns the localized string.

Below I will show some examples of how it can be helpful.

Example 1 - Text between delimiters

In the example below, I want to return the string between the delimiters “/” and “\”:

Result:

Example 2 - Retrieve the beginning of a delimited string

In the example below, I want to return the first 3 parts of an underline delimited string (_):

Result:

Example 3 - Retrieving the middle of a delimited string

In the example below, I will show how to retrieve 2 parts of an underline delimited string (_) from the 4 part:

Result:

Example 4 - Retrieve the end of a delimited string

In the example below, I will show how to retrieve the end of a string (99 parts kkkk) from the 4th part of an underline delimited string (_):

Result:

Example 5 - Retrieving only a portion of the delimited string

In the example below, I will show you how to retrieve only one part (4th part) of a semicolon delimited string (;):

Result:

Charindexada Source Code:

And here, I will make available the code of the charindexada function for you to use them in your projects, studies and tests:

That's it folks!
I hope you enjoyed this really cool function and it promises to greatly help people who have similar needs to those I demonstrated here in the article and thanks again to Brunno Araújo for the time spent developing this function and letting me post it here on the blog.

A big hug and until next time!