[AccessD] Put humpty dumpty back together again

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


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com