SQL CHARINDEX()

SQL CHARINDEX() function searches for a substring inside a string starting from a specified location.





It returns the position of the substring found in the searched string, if substring is not found it returns zero.

SYNTAX

CHARINDEX(substring, string, startlocation

substring is a substring to search in string.

string is a string to be searched.

startlocation is the position where the search will start and it is an optional.

Lets look at an example of CHARINDEX() function in SQL .

Using CHARINDEX function to get the position of character within string

Following statement uses CHARINDEX function, which returns the position of character ‘t’ within string ‘Microsoft SQL Server’.

DECLARE @stringtxt varchar(20) ='Microsoft SQL Server'

SELECT CHARINDEX('t', @stringtxt) AS CharPosition

Using CHARINDEX function to get the position of text within string

Following statement uses CHARINDEX function which returns the Position of text ‘SQL’ within string

‘Microsoft SQL Server’.

DECLARE @stringtxt varchar(20) ='Microsoft SQL Server'

SELECT CHARINDEX('SQL', @stringtxt) AS CharPosition

[Also Read SUBSTRING()]

It searches for the substring ‘SQL’ in the string ‘Microsoft SQL Server’, and returns the position of substring  ‘SQL’ is at 11th. 

Using CHARINDEX to search from specified position within string

You can also specify a search position in CHARINDEX to search for a text from specified position within string. For this you need to pass a third argument startlocation value in CHARINDEX. 

Following statement uses a CHARINDEX, and search for text within string from a specified search position 12.

DECLARE @stringtxt varchar(20) ='Microsoft SQL SQL Server'

SELECT CHARINDEX('SQL', @stringtxt, 12) AS CharPosition

As you can see, it returns substring ‘SQL’ now at 15th position as you have passed search position 12 in CHARINDEX function that means it starts search in the string ‘Microsoft SQL SQL Server’ from 12th position and finds the first substring ‘SQL’ at 15th position within string.

Also Read..

SUBSTRING()

REPLACE()

REPLICATE()

UPPER()

LOWER()

TRIM()

LRTIM()

RTIM()




SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More




 150 total views,  1 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.