[dba-SQLServer] Append several fields into one

Mark Breen marklbreen at gmail.com
Thu Jun 25 02:56:24 CDT 2009


Hi John,
Great, but in your comment below, you mention printing them out.  I
was referring more to writing your own UDF's.  It is as easy as a sproc, but
can be used inline.  For the example you had originally posted in this
thread, you could write a udf that would concatenate the fields you need,
with nulls taken care of, then that udf is available throughout the db.
 Again, I guess you now this, but if it helps, I am glad.

As Arthur mentions below, you can have a udf that returns a value, or you
can also have one return a table.

Does any one know why you use a UDF to return a table, rather than an sproc
?

Mark





2009/6/24 jwcolby <jwcolby at colbyconsulting.com>

> 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
> >
> >
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>



More information about the dba-SQLServer mailing list