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.

External and Internal address of a SQL database. Please HELP!

Ok,

I am a webdesigner who at the moment does not know SQL (although, I plan on remedying that) so I am developing a page with a DB designer - he is doing the DB work, I am doing the look/feel, but he asked me the following questions, of which I cannot seem to find an answer and the guys who tend our server are useless - so I am hoping someone here can help me. In general what he needs to know is:

"what is the external address (either in domain format or IP) and the equivalent internal address so that we can access the msSQL running on your server. The internal address is needed for the webpages to talk to it, while the external address is needed for development of the pages in visual studio and the database tools." Also, he later sent me an email asking that when I get this info (from someone) that it would be usefull to get a sample string/query to get access to the DB. I am running SQL 2000 and have enterprise manager. Where can I find this information? or how do I figure this out?

Thank you for all the help -- Please let me know if you need any more info.

Hey everybody,

I am a webdesigner who currently does not know SQL (although I am working on Remedying that) So as for my question, it comes from the gentleman who I am working with on a new site. What he needs to know is:

"what is the external address (either in domain format or IP) and the equivalent internal address so that we can access the msSQL running on your server. The internal address is needed for the webpages to talk to it, while the external address is needed for development of the pages in visual studio and the database tools."

We are running SQL 2000 with Enterprise manager. On an IIS server. He would also like to get a sample string {? = Query?} for a page accessing the DB.

Any help about where to find this information would be greatly appreciated. If you need to know anything else. Please let me know...

|||http://www.connectionstrings.com/?carrier=sqlserver2005|||There is no internal and external address. I am not quite sure what you mean by that. There is also no querystring in IIS to access the db, you will have to code that for yourself. You can use any coding language and a connectionstring from www.connectionstrings.com. Hars to tell what he wants to know with this information so far.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

The 'internal' address will be the ServerName. The 'external' address will be the port and IP address -that assumes that your firewall allows that port to be open. (I suggest using something other than the default port of 1433 -choose a high number port to use.)

As Jens suggested, the web application should use the correct connectionstring. Hopefully, you will NOT allow something as reckless and dangerous as using a =?Query to pass connection information or sensitive data.