CODEWITHSUNDEEP
sqldb2
DarthOpto
DarthOpto

Reputation: 1662

DB2 Get value based on 5 character of field

I am working with a pretty badly designed table. I have a field called optional fields which for some reason has been used as a catch-all for someone who didn't want to create the table correctly.

I need to make a query where I look at this optional_fields value and do a comparison on the fifth value of the string in optional_fields.

The value from this field is something like NN14YN...N

My query would be something like:

SELECT COMPANY_NUMBER
FROM table
WHERE fifth character of OPtional Fields = 'Y'

Upvotes: 1

Views: 76

Answers (2)

bhamby
bhamby

Reputation: 15469

In addition to the great answer from @jpw, if you for some reason need to check multiple positions within the string (which I have unfortunately had to do at one time), you can use an IN, and invert the "normal" order, like so:

...
WHERE 'Y' in (
     substr(t.flags_field, 123, 1)
    ,substr(t.flags_field, 19, 1)
    ,substr(t.flags_field, 128, 1)
    ,substr(t.flags_field, 1, 1) 
)

Just thought I would share. It surprised me the first time I used it!

Upvotes: 1

jpw
jpw

Reputation: 44911

Looking at the supported string functions in DB2 (according to the documentation for DB2 for Linux UNIX and Windows 9.7.0) it would seem that substr could be used:

SELECT COMPANY_NUMBER
FROM table
WHERE substr(optional_Fields,5,1) = 'Y'

Upvotes: 3

Related Questions