[AccessD] Put humpty dumpty back together again

Yea, I saw that too.  What I didn't see is the recommended 
alternative.

John W. Colby
www.ColbyConsulting.com


Stephen wrote:
> Hate to be a wet blanket, but (see
> http://msdn.microsoft.com/en-us/library/ms176056.aspx ) this feature
> comes with a warning:
> 
> "This feature will be removed in a future version of Microsoft SQL
> Server. Avoid using this feature in new development work, and plan to
> modify applications that currently use this feature"      ;-<
> 
> 
> 
> Stephen Bond
> 
>  
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart
> McLachlan
> Sent: Saturday, 7 June 2008 1:01 p.m.
> To: Stephen
> Subject: Re: [AccessD] Put humpty dumpty back together again
> 
> 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
> 
>