LOCATE function in DB2 LUW
Posted by decipherinfosys on October 8, 2007
We had discussed the CHARINDEX() function in the previous post. Similar to the INSTR (Oracle) and CHARINDEX (MS SQL Server) functions, DB2 has a LOCATE function to search for a string within another string. It returns the starting position of the search string if it finds it within the string, else it returns 0. LOCATE also takes three arguments:
LOCATE(<Search String>,<Actual String>, <Starting Position>)
<Search String> is the string to be searched.
<Actual String> is the string in which we need to look for search string.
<Starting Position> position from which search should start. If it is not given, search starts from position 1.
Let’s create a sample table and populate it with test data:
CREATE TABLE INPT_STATE
– Tablespace 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 out state and country from the comma delimited string.
SELECT substr(state,1,locate(‘,’,state)-1) as State,
substr(state,locate(‘,’,state)+1, length(state)- locate(‘,’,state))) as Country
And here is the output.
Sorry, the comment form is closed at this time.