CHARINDEX function in SQL Server

In of our previous blog post, we had discussed the INSTR function and how it can be used for finding out the string within another string in Oracle.  MS SQL Server has a similar function called CHARINDEX which can perform the same operation as the INSTR function.

It takes three arguments.

CHARINDEX(<Search String>, <Actual String>, <Starrt Position>)
<Search String> is the string or expression to be to be searched.
<Actual String> is the string or expression in which we need to look for search string.
<Starting Position> position from which search should start. If it is not given or negative or 0, search starts from starting position of <Actual String>

Let us create a table and populate it with sample data.

CREATE TABLE INPT_STATE
(
STATE VARCHAR(50)
)
– Filegroup Clause
GO

INSERT INTO INPT_STATE VALUES(‘GEORGIA,USA’);
INSERT INTO INPT_STATE VALUES(‘TEXAS,USA’);
INSERT INTO INPT_STATE VALUES(‘CALIFORNIA,USA’);
INSERT INTO INPT_STATE VALUES(‘ALBERTA,CANADA’);
INSERT INTO INPT_STATE VALUES(‘ONTARIO,CANADA’);

Following is the SQL to find city and state. You can run it from query analyzer.

SELECT substring(state,1,charindex(‘,’,state)-1) as State,
substring(state,charindex(‘,’,state)+1, len(state)- charindex(‘,’,state))) as Country
FROM inpt_state
GO

And here is the output:

State Country
—— ———-
GEORGIA USA
TEXAS USA
CALIFORNIA USA
ALBERTA CANADA
ONTARIO CANADA