LOCATE function in DB2 LUW

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
(
STATE VARCHAR(50)
)
– 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
FROM inpt_state;

And here is the output.

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

About these ads