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.

Extra Page for a Key or Legend

I would like to know if I can create an extra page in a dynamic report that
will appear as the final page in a report and house the Key Legend and
glossary for the contained report.
I can think of two ways in theory that it can be done one by being able to
manipulate the page break to creak a final page and another to conbine two
seperate reports at runtime.
This is however a theory and I by no means think either of these is possible.
Does anyone have any Ideas at all on this?
Please and thank you.How about putting this information in its own table at the end of what you
currently have and then going into the properties of the table and choose
page break before this table? I've done this before.
"pcrtrg" wrote:
> I would like to know if I can create an extra page in a dynamic report that
> will appear as the final page in a report and house the Key Legend and
> glossary for the contained report.
> I can think of two ways in theory that it can be done one by being able to
> manipulate the page break to creak a final page and another to conbine two
> seperate reports at runtime.
> This is however a theory and I by no means think either of these is possible.
> Does anyone have any Ideas at all on this?
> Please and thank you.

Extended stored procedure performance tuning

We have an application that is based on several extended stored
procedures. When we run our application in house, or when most other
customers run it, they see performance of about X transactions per
second. One customer is seeing performance of about X/5, and I'm
having a hard time troubleshooting it. The performace bottleneck has
been narrowed to the execution of the extended stored procedures. Does
anyone know of tuneable SQL Server parameters that may specifically
affect the performance of extended stored procedures. I know the
procedures get run by a scheduler. Is there some way the priority or
frequency of the scheduler can be modified? Thanks for any advice.Are there lots of concurrent users in this one customer? This KB article
might be interesting
to you:

http://support.microsoft.com/?kbid=836839

--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix

<bsandell@.gmail.com> wrote in message
news:1116439417.870512.217270@.g14g2000cwa.googlegr oups.com...
> We have an application that is based on several extended stored
> procedures. When we run our application in house, or when most other
> customers run it, they see performance of about X transactions per
> second. One customer is seeing performance of about X/5, and I'm
> having a hard time troubleshooting it. The performace bottleneck has
> been narrowed to the execution of the extended stored procedures. Does
> anyone know of tuneable SQL Server parameters that may specifically
> affect the performance of extended stored procedures. I know the
> procedures get run by a scheduler. Is there some way the priority or
> frequency of the scheduler can be modified? Thanks for any advice.|||Are you using synchronization objects inside the XP?
Loopback connections?

GertD@.SQLDev.Net

Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.

<bsandell@.gmail.com> wrote in message
news:1116439417.870512.217270@.g14g2000cwa.googlegr oups.com...
> We have an application that is based on several extended stored
> procedures. When we run our application in house, or when most other
> customers run it, they see performance of about X transactions per
> second. One customer is seeing performance of about X/5, and I'm
> having a hard time troubleshooting it. The performace bottleneck has
> been narrowed to the execution of the extended stored procedures. Does
> anyone know of tuneable SQL Server parameters that may specifically
> affect the performance of extended stored procedures. I know the
> procedures get run by a scheduler. Is there some way the priority or
> frequency of the scheduler can be modified? Thanks for any advice.|||Also check if you customer is running fibers or threads? exec sp_configure
'lightweight pooling'
Verify size of the MemToLeave area, maybe your customers runs with /3GB on
or off, of uses the -g startup parameter to set the MemToLeave area size in
SQL Server

GertD@.SQLDev.Net

Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.

"Gert E.R. Drapers" <gertdATsqldevDOTnet> wrote in message
news:428c7663$0$64595$e4fe514c@.news.xs4all.nl...
> Are you using synchronization objects inside the XP?
> Loopback connections?
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> You assume all risk for your use.
> Copyright SQLDev.Net 1991-2005 All rights reserved.
> <bsandell@.gmail.com> wrote in message
> news:1116439417.870512.217270@.g14g2000cwa.googlegr oups.com...
>> We have an application that is based on several extended stored
>> procedures. When we run our application in house, or when most other
>> customers run it, they see performance of about X transactions per
>> second. One customer is seeing performance of about X/5, and I'm
>> having a hard time troubleshooting it. The performace bottleneck has
>> been narrowed to the execution of the extended stored procedures. Does
>> anyone know of tuneable SQL Server parameters that may specifically
>> affect the performance of extended stored procedures. I know the
>> procedures get run by a scheduler. Is there some way the priority or
>> frequency of the scheduler can be modified? Thanks for any advice.
>>sql