[AccessD] Put humpty dumpty back together again

jwcolby jwcolby at colbyconsulting.com
Fri Jun 6 09:43:05 CDT 2008


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
>>
> 
> 
> 



More information about the AccessD mailing list