Systems Engineering and RDBMS

CHARINDEX function in SQL Server

Posted by decipherinfosys on October 8, 2007

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

3 Responses to “CHARINDEX function in SQL Server”

  1. […] CHARINDEX function in SQL Server […]

  2. […] as well. Now, if we use the SUBSTRING (SQL Server) – SUBSTR in Oracle function and make use of the CHARINDEX (SQL Server) or INSTR (Oracle) or LOCATE (DB2 LUW), we can easily do these manipulations. Let us […]

  3. Look At This

    CHARINDEX function in SQL Server « Systems Engineering and RDBMS

Sorry, the comment form is closed at this time.

 
%d bloggers like this: