SQL Server Substring with CharIndex

In this article we are going to explore the T-SQL function CharIndex and also how to use it with another T-SQL function Substring().

CharIndex: This function returns the location of a substring in a string. By default, it is not case sensitive and is useful to check the position of a particular character or digit in the string. I have seen this function being used in product codes, different types of IDs for validation purpose, or to get some business understanding of that code/ID. Here’s the syntax:

CHARINDEX( Substring or Character, string, [start_position] )

where..

Substring or Character - The string that you want to find. It has to be within string data type family such as char or varchar only.
String - The string or number or an alphanumeric value to search within.
start_position - Optional. The position in string where the search will start. The first position is 1.

The function returns bigint if the expressionToSearch is of varchar(max), nvarchar(max), or varbinary(max) data types; otherwise, it retuns an int.

Substring with CharIndex examples:

select charindex('C','ABCDEF',1) 
-- Returns 3

select charindex('C','ABCDEF') 
-- Still returns 3 as start_position is option, default is 1

select charindex('c','ABCDEF',1) 
-- By default not case sensitive, so returns 3

select charindex('c','ABCDEF' COLLATE Latin1_General_CS_AS) 
–- Returns 0 as lowercase c is not found.

select charindex('Z','ABCDEF',1) 
-- Returns 0 as there is no Z character

select charindex('A','ABCDAEFG',2) 
-- Returns the second position of A, i.e. 5

select charindex('A','ABCDAEFG',9) 
-- Returns 0 as there is no ninth position there is no A

select charindex('4',123456) 
-- Searching from pure numbers, returns 4 as 4 is on fourth position

select charindex('curry','sqlservercurry.com') 
-- It considers the first character of string curry, 
-- i.e. c and then checks the first occurrence, so returns 10

Using CharIndex with Substring:

First let us check the functionality of the Substring function. It extracts a part of the string from the specified position.

Syntax:

Substring(String, Position or Index, Number of characters to extract)

select substring('abcdef', 2, 3) 
-- Returns bcd as 2 is from which position and 3 is number of characters to extract

Now the best usage of CharIndex with Substring is to create a functionality of “Text to Columns”.

That means many a times the names are in the format of “FirstName whitespace LastName” in the same column itself. In such cases, the need is to separate First Name in one column, and Last Name in another column for display purposes.

Consider the following example.

create table Authors
(AuthorID Varchar(2),
 AuthorName Varchar(60)
 )
 go
Insert into Authors Values('A1','Suprotim Agarwal')
Insert into Authors Values('A2','Mahesh Sabnis')
Insert into Authors Values('A3','Mandar Mulay')
go
select * from Authors
go

Now let us say we want to display first name and last name in different columns. For that, we will use the substring function and within it, we will use CharIndex function to know the position number of white space.

See the following query:

select substring(AuthorName,1, charindex(' ',AuthorName)) as [First Name],
substring(AuthorName,(charindex(' ',AuthorName)) + 1, len(AuthorName)) as [Last Name]
from Authors
go

Explanation:

First Name - substring(AuthorName,1, charindex(' ',AuthorName)) means from the first position till the first white space comes extract all the characters.

Last Name - substring(AuthorName,(charindex(' ',AuthorName)) + 1, len(AuthorName)) means to extract from the first white space + 1. Then to extract all remaining characters the len function is used.

And that’s how we use the SQL Server T-SQL Substring function, as well as Substring with CharIndex.