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 white space in table

I've got a table with a header, group, and detail row. The detail row
is collaspable however when it's collasped there is several lines of
white space between the parent groups. How do I get rid of this white
space?
Thanks in advance for the helpIs it possible that the white space is really the detail row, and it is
not shrinking as expected? (I've had this same problem before...) If
you are supressing just a column or two, but not hiding the entire
detail row (such as setting visibility --> hidden property based upon
conditional expression) then you will get the spacing issue as
described.|||Thanks Matt
That was the problem. I was setting the visibility at the cell level
when i should have been setting it on the over all row. thanks for the
help
Mathiassql

Extra White Space

Hi All,
I've got a report that is set up with the Report Properties layout as
follows: page width = 8.5in; page height = 11in; left/right margins = .25in;
top/bottom margins = .50in.
When the report displays in IE7, there is a large white area (blank) to the
right of the report and it's full length. When I export as a PDF document,
this area seems to cause extra pages to be created in the PDF output.
Since I'm not sure "why" this extra white space is appearing, I thought I'd
post this here.
Thanks in advance!
Kind regards - FredHi Fred,
I've found, that meters of extra white space on the right side, before all
when viewed in HTML, are created when there are overlapping visible textboxes.
When textboxes overlap where one is invisible, there are also minor layout
differences, but never so dramatically.
Yours, Henry

Extra Space....

Hello!

I have written a report that is being displayed in a table. For some reason all the way to the right of the table has a HUGE amount of space......I checked to make sure there was no layers over it and I looked in the properties and saw no padding....ANY IDEAS? I am so frusterated because this defies all logic!!! Has anybody experienced this?

TIA!

That happened to me and I checked the width of the object that contained the table. In my case, the width of the rectangle that contained my table was way bigger than it should have been.|||

Oh Thank you for responding!! Are you talking about the width of the table itself....or the width of the page?

Thanks Again!

|||

Hi,

Check what the parent of the table is. If it says "body", then that is what you would adjust the width of.

|||Oh great! thanks!

Extra space at bottom of page

I keep getting extra space at the bottom of each page on my report when it is deployed to the web server but I don't get this extra space on the report server. Does anyone know if this is in the report layout or if the problem is in the web page itself?

Thanks,

messed around with report printer properties and re-deployed.sql

extra space at bottom

Hi;
i want to add extra separation between lines when a field has certain
value... right now, i tried bottom padding and Lineheight but none seems to
work...
this is the expression i had on my table detail's line bottom pading :
=iif(fields!UTIL.value="T",10,2)
Any advice?found the problem...
i has to be: =iif(fields!UTIL.value="T","10pt","2pt")
"Willo" <willoberto@.yahoo.com.mx> wrote in message
news:%235Wc$9FgHHA.668@.TK2MSFTNGP05.phx.gbl...
> Hi;
> i want to add extra separation between lines when a field has certain
> value... right now, i tried bottom padding and Lineheight but none seems
> to work...
> this is the expression i had on my table detail's line bottom pading :
> =iif(fields!UTIL.value="T",10,2)
> Any advice?
>
>

Extra Lines in ReportViewer (lots of em)

Any idea where these extra lines are coming from in the detail? The data is being summarized correctly. I just need to dump all the extra space. I'm one stumped kahuna.

Maybe the datasource is returning a bunch of empty strings. I assume this table is set to grow with the data.|||I thought that, too, and checked it out. In one or two cases it is returning just a couple empty strings but nothing like what I'm seeing here.

extended procedures

I'm finding lots of code samples for writing extended procs, but *all* of
them
do nothing with SQL. Hello or calculating free disk space are common
examples.
I'm writing a sp because I want to work with SQL data. How do I do
that from an xp?
Do I have to go back around through ADO?
I would think that ODS would have a method in the API to interact with
the data, but you can't tell that from the sample code I've found so far.
Thanks,
Brad.Normally an XP will perform functions that aren't available (or aren't fast
enough) via standard T-SQL. If all you want to do is execute standard T-SQL
statements against your database, you probably want to write a regular SP.
"Brad White" <bwhite at inebraska . com> wrote in message
news:%23v1TOVwYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> I'm finding lots of code samples for writing extended procs, but *all* of
> them
> do nothing with SQL. Hello or calculating free disk space are common
> examples.
> I'm writing a sp because I want to work with SQL data. How do I do
> that from an xp?
> Do I have to go back around through ADO?
> I would think that ODS would have a method in the API to interact with
> the data, but you can't tell that from the sample code I've found so far.
> --
> Thanks,
> Brad.
>
>