CODEWITHSUNDEEP
stringoracle-databasefindcontains
Raghavendar juluru
Raghavendar juluru

Reputation: 11

In oracle How to find if a particular value exists in a string which contains multiple values seperated by comma(,)

I would like to find if TD003 or BA999 are avaialble in a particular column.

There is a possiblity of this column containing more than 20 values and I would like to check for a possible list of 100 values.

Upvotes: 1

Views: 4334

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

I suggest you use the INSTR function:

SELECT *
  FROM SOME_TABLE
  WHERE INSTR(COMMA_SEPARATED_VALUES_FIELD, 'TD003') > 0 OR
        INSTR(COMMA_SEPARATED_VALUES_FIELD, 'BA999') > 0

Best of luck.

EDIT

Let's consider some sample data from this SQLFiddle:

CREATE TABLE SOME_TABLE(COMMA_SEPARATED_VALUES_FIELD  VARCHAR2(100));

INSERT INTO SOME_TABLE (COMMA_SEPARATED_VALUES_FIELD) VALUES ('NOT THIS ONE');
INSERT INTO SOME_TABLE (COMMA_SEPARATED_VALUES_FIELD) VALUES ('XYZ,ZTD003Y,ZZZ');
INSERT INTO SOME_TABLE (COMMA_SEPARATED_VALUES_FIELD) VALUES ('12345BA99999999');
INSERT INTO SOME_TABLE (COMMA_SEPARATED_VALUES_FIELD) VALUES ('OR THIS ONE');
INSERT INTO SOME_TABLE (COMMA_SEPARATED_VALUES_FIELD) VALUES ('BUT THIS ONE TD003 IT''LL FIND JUST FINE');
INSERT INTO SOME_TABLE (COMMA_SEPARATED_VALUES_FIELD) VALUES ('AS WELL ASBA999THIS ONE');

Given this data, the SQL statement above returns:

COMMA_SEPARATED_VALUES_FIELD
XYZ,ZTD003Y,ZZZ
12345BA99999999
BUT THIS ONE TD003 IT'LL FIND JUST FINE
AS WELL ASBA999THIS ONE

INSTR returns the position of the search string (e.g. 'TD003') within the target string, so for example when the target string is

XYZ,ZTD003Y,ZZZ

INSTR('XYZ,ZTD003Y,ZZZ', 'TD0003') returns 6, which is the index of the first character of TD0003 within the target string. If the search string is not found within the target string zero is returned.

Upvotes: 2

Related Questions