Extracing a House Number

I am trying to Extract the House Number from a address field

i want to start on the left and grab everything till i find the first space.

any help is greatly appreciated

select LEFT(address, (CHARINDEX(' ', address)-1)) as HouseNum

from Table

|||

Could you post some sample address data with house/unit/apt #? Note that this will be a tough problem to solve depending on your address data. It is easy to write specific SQL/TSQL expressions to extract various parts of the address but the possibilities are numerous depending on the data. There are many variables like following:

Does the addresses belong only to the USA?

Do you have different line numbers for the address that can contain the unit#?

What about the format of the addresses?

Are the addresses normalized in any manner?

So this can be a non-trivial approach and depending on your requirements it will be tough to solve in TSQL. It is best to do the address cleansing outside of the database. There are lot of commercial packages / products available to do this type of data cleaning. They can easily correct address data and normalize them to various fixed formats depending on the type of address. You can then get individual fields like zip, city, state, unit#, street# etc and use it in your application.

|||Agree. My SQL statement is only used to extract information from a string. Don should base on his situation to decide which is the best solution.|||

if the objective is only to get the house number only then

he should grab the first consecutive non alpha characters instead

save the results and have an encoder to the dirty job

|||thanks all the suggestion helped alot.