CODEWITHSUNDEEP
javasqldb2
Kishore_2021
Kishore_2021

Reputation: 699

Select query with Substring & Instr in DB2

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.

Upvotes: 0

Views: 1962

Answers (3)

ankitaP
ankitaP

Reputation: 85

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

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

Upvotes: 1

bonCodigo
bonCodigo

Reputation: 14361

Try this too: With Right and upper:

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

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;

Upvotes: 1

Neji
Neji

Reputation: 6839

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

Upvotes: 1

Related Questions