[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2244
  • Last Modified:

Best way to determine even/odd using SQL

Hi

I have two text fields in a table called STREET_NUMBER_FROM and STREET_NUMBER_TO that only have numerical characters, what is the fastest SQL method for determining if one or both of the fields represents an even or odd number?

Thanks and take care,
Shayne
0
shaynegw
Asked:
shaynegw
  • 3
1 Solution
 
oratimCommented:
select decode(mod(STREET_NUMBER_FROM,2),1,'ODD',0,'EVEN','ERROR') from street_numbers

0
 
shaynegwAuthor Commented:
Hi

I made a mistake, there are characters in these fields, so I have to deal with values like 1A, 2B, 123C, etc. So I'll also need a method to filter out the characters before determining odd/even.

Take care,
Shayne
0
 
oratimCommented:
sorry,

if they are text use this

select decode(mod(to_number(STREET_NUMBER_FROM),2),1,'ODD',0,'EVEN','ERROR') from street_numbers

but that will only work if the data is numeric, so 123A will cause error ORA-01722: invalid number

0
 
oratimCommented:
select street_number_to,
           translate(lower(street_number_to),' abcdefghijklmnopqrstuvwxyz0123456789.-','                           0123456789.'),
           decode(mod(to_number(translate(lower(street_number_to),' abcdefghijklmnopqrstuvwxyz0123456789.-','                           0123456789') ),2) ,1,'odd',0,'even','error' )      from street_numbers


HTH
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now