[AccessD] Put humpty dumpty back together again

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



More information about the AccessD mailing list