Select query with Substring & Instr in DB2

Question

Please see the image I want to Select only those records of table TEST, having TEMPPATH value ends with IN ('XGENCISB.CPY', 'XCISTABT.CPY').

NOTE: These IN clause values will be set at run time

Please tell if this possible in a single query using sub-string??

Thanks in advance.

Is this possible to look up the name of files after last "\" char in a single SELECT query e.g. First pick the all names from last "\" and then search the given names in a IN clause. — Kishore_2021, Jan 23 '13 at 05:28

score 1 · Answer 1 · answered Jan 22 '13 at 10:28

1

select * from TEST where TEMPPATH LIKE '%.CPY' OR TEMPPATH LIKE '%.cpy'

answered Jan 22 '13 at 10:28

Neji

Thx @Neji, But what about if any record having lower case data e.g. ".cpy" or ".CpY" type data. USACE can be applied on where condition?? – Kishore_2021 Jan 22 '13 at 10:32

score 1 · Accepted Answer · edited May 23 '17 at 12:03

1

Try this too: With Right and upper:

select * from TEST where upper(Right(TEMPPATH,4)) = '.CPY'
AND TEMPPATH IS NOT NULL;

If you are interested in regexp, take a look at this as well: Regular Expressions in DB2 SQL

EDIT as per OP's udpates on the question

Given that theset two string will be in the same length(12 or adjust accordingly), you can try

select * from TEST where upper(Right(TEMPPATH,12)) 
IN ('XGENCISB.CPY', 'XCISTABT.CPY')
AND TEMPPATH IS NOT NULL;

edited May 23 '17 at 12:03

Community

answered Jan 22 '13 at 10:30

bonCodigo

@Kishore this one will bring both into `upper case` :) – bonCodigo Jan 22 '13 at 10:34
Happy to hear you got it worked :D a note on `index`, if you will be doing this search in large scale it's best that you [`reverse`](http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=%2Fcom.ibm.db2.luw.xml.doc%2Fdoc%2Fxqrfnrvs.html) this string and store it in another column giving an index. Makes things perform better. – bonCodigo Jan 22 '13 at 10:43
Actually my requirement was to select multiple records in 1 query, as I updated the question, plz tell if you know. – Kishore_2021 Jan 22 '13 at 14:07
@Kishore I have updated accordingly, give it try and let us know :) – bonCodigo Jan 22 '13 at 14:11
Is this possible to look up the name of files after last "\" char in a single SELECT query e.g. First pick the all names from last "\" and then search the given names in a IN clause. – Kishore_2021 Jan 23 '13 at 05:27

ankitaP · Answer 3 · 2013-01-22T15:46:36.797

1

This is the solution of the CASES you asked from @Neji:

    select * from TEST where lower(TEMPPATH) LIKE (lower('%.cpy'));

edited Jan 22 '13 at 15:46

answered Jan 22 '13 at 10:45

ankitaP