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
— Filegroup Clause
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
And here is the output:
3 Responses to “CHARINDEX function in SQL Server”
Sorry, the comment form is closed at this time.