jwcolby
jwcolby at colbyconsulting.com
Wed Jun 24 06:50:53 CDT 2009
Mark, I know that they exist, and I have used them very occasionally. I really need to go become acquainted with all of them, print out a list and study what is available. I am doing rather a lot of stored procedures now, but they mostly just build tables, add fields, indexes, bcp in/out etc. I am still at the advanced beginner level in SQL Server. John W. Colby www.ColbyConsulting.com Mark Breen wrote: > 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 >> >> > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com > >