Stuart McLachlan
stuart at lexacorp.com.pg
Fri Jun 6 19:46:39 CDT 2008
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