CODEWITHSUNDEEP
sqloracle-database
HalfWebDev
HalfWebDev

Reputation: 7638

SQL function Instr()

I am having a column named DP as shown:

 07-APR-2011
 12-APR-2011
 26-APR-2011

Now to retrieve the query for selecting the payments made in the month of april i came across a query 

select * from payments where instr(dp,'APR')<>0

Okay , i am well acquainted with INSTR function and > sign , but cant interpret the logic with<> sign here !

[UPDATE]

i am also aware that <> is equivalent of != . But my point is we could have used
instr(dp,'APR') instead of doing instr(dp,'APR')<>0

Upvotes: 2

Views: 9588

Answers (2)

ruakh
ruakh

Reputation: 183261

<> means "is not equal to". You can also write !=, if you prefer.

instr(dp,'APR') returns zero if 'APR' is not a substring of dp, so instr(dp,'APR')<>0 means "'APR' is a substring of dp". It could also be written as dp LIKE '%APR%'.

Update for updated question:

But my point is we could have used instr(dp,'APR') instead of doing instr(dp,'APR')<>0

No, you couldn't have. Some dialects of SQL treat zero as "false" and other integers as "true", but Oracle does not do this. It treats integers and Booleans as separate types, and does not implicitly convert between them. WHERE 0 is not a valid WHERE-clause.

Upvotes: 7

JNK
JNK

Reputation: 65147

<> is Not Equals - basically it's checking that a substring of 'APR' appears in the string.

If that function returned 0 then it would indicate 'APR' does not appear anywhere in the string to be searched.

Upvotes: 3

Related Questions