jwcolby
jwcolby at colbyconsulting.com
Sat Jun 7 08:19:38 CDT 2008
I did not try it in the query itself. I did try it in the database properties. John W. Colby www.ColbyConsulting.com Stuart McLachlan wrote: > Have you tried setting explicitly immediately before your select? > ie SET CONCAT_NULL_YIELDS_NULL ON > > > On 6 Jun 2008 at 10:43, jwcolby wrote: > >> I found a database property / options / Concat null yields >> null, which it CLAIMS will change this behavior. However it >> still yields a null regardless of whether I set this to true >> or false. >> >> Ths ISNULL() thing no doubt works but I have to concat 7 >> different fields for 84 million records. I suspect that I >> would be setting myself up for a week long query. >> >> Given that the property makes no difference I may be forced >> to do this however. >> >> John W. Colby >> www.ColbyConsulting.com >> >> >> Paul Hartland wrote: >>> Off the top of my head try: >>> >>> Select ISNULL(HouseNumberPrefix,'') + ' ' + ISNULL(HouseNumber,'') + ' ' + >>> ISNULL(HouseNumberSuffix,'') + ' ' + ISNULL(Direction,'') + ' ' + >>> ISNULL(StreetName,'') + ' ' + ISNULL(Mode,'') + ' ' + ISNULL(Quadrant,'') + >>> ' ' + ISNULL(Appt#,'') >>> >>> Paul Hartland >>> >>> 2008/6/6 jwcolby <jwcolby at colbyconsulting.com>: >>> >>>> I have a database that has split the address line into >>>> >>>> HouseNumberPrefix >>>> HouseNumber >>>> HouseNumberSuffix >>>> Direction >>>> StreetName >>>> Mode (N, NW etc) >>>> Quadrant >>>> Appt# >>>> >>>> I need to put Humpty back together again to feed off to >>>> Address Validation. How would I do that in SQL? >>>> >>>> I THINK I can just append them all together with spaces >>>> between the parts and that would be fine EXCEPT that when >>>> you do something like NULL + SomeString you end up with null. >>>> >>>> How would I do what I am trying to do in SQL? >>>> >>>> -- >>>> John W. Colby >>>> www.ColbyConsulting.com <http://www.colbyconsulting.com/> >>>> -- >>>> AccessD mailing list >>>> AccessD at databaseadvisors.com >>>> http://databaseadvisors.com/mailman/listinfo/accessd >>>> Website: http://www.databaseadvisors.com >>>> >>> >>> >> -- >> AccessD mailing list >> AccessD at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/accessd >> Website: http://www.databaseadvisors.com > >