jwcolby
jwcolby at colbyconsulting.com
Tue Jun 23 10:27:08 CDT 2009
This certainly looks like the answer. Now to turn the computer loose for a week, updating the address field... There are about 6 or 8 of these address parts, each will now be inside of an IsNull(). ;) Is it possible to do this X records at a time? IOW I have a fifty million record table. I want to cause the computer to go off and start doing this but I also want the table that is being updated to be available for use while this is happening (for selection queries). How do I tell SQL Server to do updates in blocks of 1000 records or something like that? Can I dynamically change that "number of records updated at a time" inside of a stored procedure? John W. Colby www.ColbyConsulting.com Francisco Tapia wrote: > John, you can do it right out of a select, something like this > > IsNull(StreetNumber + ' ', '') + IsNull(Direction + ' ', '') + > IsNull(StreetName + ' ', '') etc... > > I added the space padding right inside of the IsNull formula because this > way you get a padding, for each field, unless there is nothing then the > result is no padding. In Sql Server Null + a value = Null :) > > If you don't want to accidentally end up with a space padding on the right > simply trim your statement with RTRIM(your isnull series) > > > > -Francisco > http://sqlthis.blogspot.com | Tsql and More... > > > On Tue, Jun 23, 2009 at 6:53 AM, jwcolby <jwcolby at colbyconsulting.com>wrote: > >> I am trying to append all of the pieces of an address into a single address >> string. As an example, >> if I have the address: >> >> 121 S. Main Street NE Apt #201 >> >> this address will be stored with standardized spellings of all the parts >> into a set of fields: >> >> 121 >> S >> Main >> St >> NE >> Apt >> 201 >> >> I need to get this all back into a single field. Understand however that >> lots of those pieces might >> be missing in any given address. For example the next field might be: >> >> 121 >> >> Main >> St >> >> Ste >> 1342 >> >> So... I need to get all the pieces back into a single address field. If a >> piece exists then it >> needs padding around it so I don't end up with: >> >> 121MainStSte1342 >> >> But if a piece does not exist then I do not want the padding for that >> missing field. >> >> I am assuming that I will be using a stored procedure. Pass in the >> database, table, >> UnifiedAddressField, then all of the field parts. >> >> It seems clumsy to do it this way, not to mention record by record, for 50 >> million records... well... >> >> There must be a better way. Has anyone on the list done this before? >> >> -- >> John W. Colby >> www.ColbyConsulting.com >> _______________________________________________ >> dba-SQLServer mailing list >> dba-SQLServer at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >> http://www.databaseadvisors.com >> >> > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >