combine text in sql i think?

Given the following SQL statement shown below. I do not get anything returned for the "image_link" in this line of code

,  'http://www.thesite.com/v/vspfiles/photos/' + IsNull(p.Vendor_PartNo,p.ProductCode) + '-2.jpg' AS image_link

If I change this line to the following (removing the "." in -2.jpg) then I get returned the complete link 开发者_JAVA技巧(prefix and suffix with ) but obviously without the "."

,  'http://www.thesite.com/v/vspfiles/photos/' + IsNull(p.Vendor_PartNo,p.ProductCode) + '-2jpg' AS image_link

What is wrong and how can I include the "." in the suffix?

SELECT p.ProductCode AS id
    ,  p.ProductName AS title
    ,  'Home & Garden > Household Appliance Accessories > Laundry Appliance Accessories' AS product_type
    ,  IsNull(pe.SalePrice,pe.ProductPrice) AS price
    ,  IsNull(pe.ProductManufacturer,'n/a') AS brand
    ,  IsNull(pe.ProductCondition,'new') AS condition
    ,  CONVERT(VARCHAR(10), (GETDATE() + 30),120) AS expiration_date
    ,  pd.ProductDescriptionShort AS [stripHTML-description]
    ,  'http://www.thesite.com/v/vspfiles/photos/' + IsNull(p.Vendor_PartNo,p.ProductCode) + '-2.jpg' AS image_link
    ,  'http://www.thesite.asp?ProductCode=' + p.ProductCode + '&Click=1327'  AS link
    , CAST(pe.ProductWeight AS VARCHAR(20)) + ' lb' AS weight
 FROM Products p
 JOIN Products_Descriptions pd ON p.ProductID = pd.ProductID
 JOIN Products_Extended pe ON pd.ProductID = pe.ProductID
WHERE (p.IsChildOfProductCode is NULL OR p.IsChildOfProductCode = ' 
       AND (p.HideProduct is NULL OR p.HideProduct <> 'Y')
  AND pe.ProductPrice > 0
ORDER BY p.ProductCode

In SQL-Server (Microsoft), simply use the + sign and char(46) to get the period

'http://www.thesiste/v/vspfiles/photos/'+
    isNull(p.vendor_partno,p.productCode)+'-2'+char(46)+'jpg'

See if the http: is causing the issue, perhaps the server is treating the link differently

'ht'+'tp://www.thesiste/v/vspfiles/photos/'+
    isNull(p.vendor_partno,p.productCode)+'-2'+char(46)+'jpg'

Could also try

replace('http:||www.thesiste|v|vspfiles|photos|','|','/')+
    isNull(p.vendor_partNo,p.productCode)+'-2'+char(46)+'jpg'

in case the slash character is causing a problem

It appears the slash character is being treated special. Try creating a variable with the beginning value and use that...

DECLARE @URL varchar(200)
SET @Url = 'http://www.thesiste/v/vspfiles/photos/'

    @url + isNull(p.vendor_partno,p.productCode)+'-2'+char(46)+'jpg'

Or even, worse try,

    'http:'+char(47)+char(47)+'www.thesiste'+char(47)+'v'+char(47)+'vspfiles'+
char(47)+'photos'+char(47)'+
        isNull(p.vendor_partno,p.productCode)+'-2'+char(46)+'jpg'

Have you tried this?

CONCAT('http://www.thesite.com/v/vspfiles/photos/', IsNull(p.Vendor_PartNo,p.ProductCode),'-2.jpg') AS image_link

I haven't tested this, but off the top of my head, do you think you are having a data type problem?

'http://www.thesite.com/v/vspfiles/photos/' + Cast(IsNull(p.Vendor_PartNo,p.ProductCode) AS varchar) + '-2.jpg' AS image_link