Problems Concatenating Columns

Hi All,
I am getting some rather strange results when concatenating columns.
Please see my inline comments below...
SELECT
'http://www.oursite.co.uk/scripts/prodview.asp?idproduct=' +
CAST(products.idProduct AS Char(4)) AS product_url,
('Replacement '
+
Case When CharIndex(';',Products.description) > 0 Then
Left(Products.description,CharIndex(';',Products.description)-1)
Else
Products.description
End
+
' for '
+
Case When CharIndex('[',allProducts.CatDescMake) > 0 Then
Left(allProducts.CatDescMake,CharIndex('[',allProducts.CatDescMake) - 1)
+'' -- If I dont add this empty string, the concatenation seems to stop
after this point (not so much of a problem, but why?)
Else
allProducts.CatDescMake + '' -- Ditto!
End
--+ allProducts.CatDescModel + products.description -- This fails, only
the
first column ends up in the concatenated column
+ products.description + allProducts.CatDescModel -- This works OK, both
columns are in the concatenated column
) AS name,
products.DescriptionLong as Description,
'http://www.oursite.co.uk/prodimages/' + products.imageURL as image_url,
products.Price as price
FROM Categories_Products
INNER JOIN allProducts ON Categories_Products.idCategory =
allProducts.CatIDModel
INNER JOIN products ON Categories_Products.idProduct = products.idProduct
WHERE products.idProduct IN (select idProduct from products where
description like '%;%')
Any help will be appreciated! :)
Simon.
PS: I know that string concatnation should be done on the
client/application
end, but ASP is not happy about doing with with the amount of records we
have (Times out) where as from what I have seen so far, SQL Server does
the
job ALOT faster!
---
I am using the free version of SPAMfighter for private users.
It has removed 2988 spam emails to date.
Paying users do not have this message in their emails.
Try www.SPAMfighter.com for free now!Hi
Check that those columns you are having problems with do not contain nulls.
string + NULL results in NULL
You might need to add to your code ISNULL(column, '') replaces a null with
an empty string.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
news:eEeSzzvAGHA.3456@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I am getting some rather strange results when concatenating columns.
> Please see my inline comments below...
> SELECT
> 'http://www.oursite.co.uk/scripts/prodview.asp?idproduct=' +
> CAST(products.idProduct AS Char(4)) AS product_url,
> ('Replacement '
> +
> Case When CharIndex(';',Products.description) > 0 Then
> Left(Products.description,CharIndex(';',Products.description)-1)
> Else
> Products.description
> End
> +
> ' for '
> +
> Case When CharIndex('[',allProducts.CatDescMake) > 0 Then
> Left(allProducts.CatDescMake,CharIndex('[',allProducts.CatDescMake) - 1)
> +'' -- If I dont add this empty string, the concatenation seems to stop
> after this point (not so much of a problem, but why?)
> Else
> allProducts.CatDescMake + '' -- Ditto!
> End
> --+ allProducts.CatDescModel + products.description -- This fails, only
> the
> first column ends up in the concatenated column
> + products.description + allProducts.CatDescModel -- This works OK, both
> columns are in the concatenated column
> ) AS name,
> products.DescriptionLong as Description,
> 'http://www.oursite.co.uk/prodimages/' + products.imageURL as image_url,
> products.Price as price
> FROM Categories_Products
> INNER JOIN allProducts ON Categories_Products.idCategory =
> allProducts.CatIDModel
> INNER JOIN products ON Categories_Products.idProduct = products.idProduct
> WHERE products.idProduct IN (select idProduct from products where
> description like '%;%')
> Any help will be appreciated! :)
> Simon.
> PS: I know that string concatnation should be done on the
> client/application
> end, but ASP is not happy about doing with with the amount of records we
> have (Times out) where as from what I have seen so far, SQL Server does
> the
> job ALOT faster!
>
> ---
> I am using the free version of SPAMfighter for private users.
> It has removed 2988 spam emails to date.
> Paying users do not have this message in their emails.
> Try www.SPAMfighter.com for free now!
>sql