Parse number from text field and test for even/odd?

Our SQL database table has a field which contains the street address --(usually a number followed by the street name though it's just a text string and it could contain anything. If the field doesn't start with a number then I'll just skip that record).

I need to determine if the street address number is even or odd. I only want to select the odd street addresses (or maybe it's the even ones I want, I forget :-)

How would I go about parsing off the street number and then testing to see if it's an even or odd number?
deleydSoftware EngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
You can use the combination of LIKE and PATINDEX to get the desired result:
    create table customers (id int identity,
                            address varchar(100));
    insert into customers(address) values('1 Main St'),
                            ('223 Main St'),
                            ('12 Main St'),
                            ('Main St 23');

Open in new window

Query:
    select *,
    n.num
    from customers
    CROSS APPLY (SELECT substring(address, 1, patindex('%[^0-9]%', address)) as num) n
    where address like '[0-9]%'
    and convert(int, n.num) % 2 = 1



    | id |     address |  num |  num |
    |----|-------------|------|------|
    |  1 |   1 Main St |   1  |   1  |
    |  2 | 223 Main St | 223  | 223  |

Open in new window


To get the even numbers modify the last line to:
convert(int, n.num) % 2 = 0

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.