I'm using STUFF to create a list with names pulled from another table. I have used the STUFF function successfully before exepct this time I am using EncryptByPassphrase functionality to encrypt certain names. The list I want returned needs to be Decrypted first. Unfortunately whats returned isn't exactly what I want even tho what I want is included in the middle. Here's a snippet of my code.
CONVERT(varchar(255),DecryptByPassphrase('test pass', cpCompanyName)) AS 'cpCompanyName',
(Stuff ((SELECT ', ' + cast(cpReferenceCustomerSiteID as nvarchar) FROM cpCompanySites sb WHERE sb.cpCompanyID=cc.cpCompanyID and sb.active=1 FOR XML PATH('')), 1, 1, '')) AS sites_list,
(Stuff ((SELECT ', ' + CONVERT(varchar(255),DecryptByPassphrase('test pass', cpReferenceCustomerSiteName)) as cpReferenceCustomerSiteName FROM cpCompanySites sb left join cpReferenceCustomerSite s on sb.cpReferenceCustomerSiteID=s.cpReferenceCustomerSiteID WHERE sb.cpCompanyID=cc.cpCompanyID FOR XML PATH('')), 1, 1, '')) AS site_names
FROM cpCompany cc
My cpCompany table has company details and my cpCompanySites tables acts as the bus table and may have mutliple site entries per company.
Whats being returned is
which is close to what should be returned. Ireland is all that should be returned so its like there is some formatting issue which I can't seem to solve.
Anyone see anything obvious why cpReferenceCustomerSiteName> and is also being returned? The cpReferenceCustomerSiteName field in the cpReferenceCustomerSite table is encrypted as are all my encryoted fields using varbinary(500)