Mark Breen
marklbreen at gmail.com
Wed Jun 24 03:23:06 CDT 2009
Hello John, you thought me how to call vba functions in MS Access 97 queries, I was shocked by how powerful that feature was. Have you used functions in SQL Server? If so, please ignore my email and accept my apologies for even asking the question;) I have created a few functions in SQL Server and for low volumes of data, they work great, of course in your case, you are seeking max efficiency and I have no idea about performance of a function versus inline code. I guess no major difference. If you have not used them, give them a try. Thanks Mark 2009/6/23 jwcolby <jwcolby at colbyconsulting.com> > 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 > > > > > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >