Format Data returned by TSQL into HTML

Question

I have the following query

Select
Trim(AP200.VENDORID) as VendorId,
Trim(AP300.ADRSCODE) as VendorLocId,
Trim(AP200.VendName) as VendorName,
CSZ=Concat(
'<p>',Trim(AP300.Address1) , '</p>',
'<p>',Trim(AP300.Address2) , '</p>',
'<p>',Trim(AP300.Address3) , '</p>',
'<p>',Trim(AP300.City) , ', ',Trim(AP300.State) , ' ',Trim(AP300.Zipcode), '</p>')

from
dbo.AP200 AP200 (nolock)
Left Join dbo.AP300 AP300 (nolock) on
AP200.VENDORID=AP300.VENDORID
Where AP300.ADRSCODE not like 'REMIT%'

• which I am just looking to return so it can be used in HTML format whereby all of the address information is concatenated and has a carriage return after each address line. What is the best way to write the TSQL statement ?

Also - how do I handle if there is no information for Address2 or Address3 ?

Thank you!

Answer 1

If the formatting rules or styles of your HTML require <p> elements, then consider this expression too:

CSZ = Concat(
   '<p>' + nullif(Trim(AP300.Address1), '') + '</p>',
   '<p>' + nullif(Trim(AP300.Address2), '') + '</p>',
   '<p>' + nullif(Trim(AP300.Address3), '') + '</p>',
   '<p>', Trim(AP300.City), ', ', Trim(AP300.State), ' ', Trim(AP300.Zipcode), '</p>' )

The empty or null address lines will be excluded. It is also possible to adjust the last line to avoid unneeded ‘,’ in case of missing City.

Answer 2

Hi @vsslasd ，

Add a carriage return after each address, you can use char(13)(CHAR (Transact-SQL))：

Select   
Trim(AP200.VENDORID) as VendorId,   
Trim(AP300.ADRSCODE) as VendorLocId,   
Trim(AP200.VendName) as VendorName,  
CSZ=Concat(  
'<p>',Trim(AP300.Address1)+ CHAR(13) , '</p>',  
'<p>',Trim(AP300.Address2)+ CHAR(13) , '</p>',  
'<p>',Trim(AP300.Address3)+ CHAR(13) , '</p>',  
'<p>',Trim(AP300.City) , ', ',Trim(AP300.State) , ' ',Trim(AP300.Zipcode), '</p>')  
  
from   
dbo.AP200 AP200 (nolock)  
Left Join dbo.AP300 AP300 (nolock) on   
AP200.VENDORID=AP300.VENDORID  
Where AP300.ADRSCODE not like 'REMIT%'  

Your question is not very clear to me. I suggest that you post the question with tables and data and the output you expect.
If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi @vsslasd ,

HTML is a subset of XML. Here is how to compose your address section via XQuery.

SQL

-- DDL and sample data population, start  
DECLARE @tbl TABLE (  
 ID INT IDENTITY PRIMARY KEY,  
 Address1 VARCHAR(50),   
 Address2 VARCHAR(50),   
 Address3 VARCHAR(50),  
 City VARCHAR(20),  
 State VARCHAR(20),  
 ZipCode VARCHAR(9)  
);  
INSERT INTO @tbl (Address1, Address2, Address3, City, State, ZipCode)  
VALUES  
('38 SW 52nd Street', NULL, NULL, 'Miami', 'FL', '33180'),  
('21170/7 Hollywood Street', 'Mapleridge', NULL, 'Austin', 'TX', '78610');  
-- DDL and sample data population, end  

  
SELECT ID  
 , CAST(N'' AS XML).query('<div>  
 <p>{sql:column("Address1")}</p>  
 <p>{sql:column("Address2")}</p>  
 <p>{sql:column("Address3")}</p>  
 <p>{concat(sql:column("City"), ", ", sql:column("State"), " ",sql:column("ZipCode") )}</p>  
 </div>').query('for $x in /div/p[text()] return $x') AS XHTML  
FROM @tbl;  

Answer 4

I'm not sure that I understand entirely what you want, but this is how you can handle that address2 and address3 may be null/blank:

CSZ='<p>' + concat_ws('<BR/>', Trim(AP300.Address1), 
                               Trim(nullif(AP300.Address2, '')),  
                               Trim(nullif(AP300.Address3, '')), 
                               Trim(AP300.City) + ', '  + Trim(AP300.State) + ' ' + Trim(AP300.Zipcode)) + '</p>'

The first argument to concat_ws is a separator, but it is not added for null inputs.

I think <BR/> is a better tag than <P> to get a single CR-LF, with <P> that could be a double. Although that depends on your style-sheet.

It is worth noting that this not what SQL Server is really target for; HTML formatting is best done client side.