Systems Engineering and RDBMS

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
(
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

One Response to “LOCATE function in DB2 LUW”

  1. notice – 2013년 5월 1일 노동절 휴일 안내

    LOCATE function in DB2 LUW « Systems Engineering and RDBMS

Sorry, the comment form is closed at this time.

 
Follow

Get every new post delivered to your Inbox.

Join 78 other followers

%d bloggers like this: