[dba-SQLServer] Concatenating Fields In SQL Stored Procedure Or SQLView

paul.hartland at fsmail.net paul.hartland at fsmail.net
Mon Feb 16 09:32:51 CST 2004


Thanks Robert.....





Message date : Feb 16 2004, 03:25 PM
>From : "Djabarov, Robert" 
To : dba-sqlserver at databaseadvisors.com
Copy to : 
Subject : RE: [dba-SQLServer] Concatenating Fields In SQL Stored Procedure Or SQLView
If the fields are NULL:

Select BranchNo, BranchCode, Address =
ClientName + 
Case Addrs1 when null then '' else ', ' + Addrs1 end +
Case Addrs2 when null then '' else ', ' + Addrs2 end +
Case District when null then '' else ', ' + District end +
Case Town when null then '' else ', ' + Town end +
Case County when null then '' else ', ' + County end +
Case Postcode when null then '' else ', ' + Postcode +
Case Telephone when null then '' else ', ' + Telephone
>From tblClient where ...

If the fields are '' then:

Select BranchNo, BranchCode, Address =
ClientName + 
Case Addrs1 when '' then '' else ', ' + Addrs1 end +
Case Addrs2 when '' then '' else ', ' + Addrs2 end +
Case District when '' then '' else ', ' + District end +
Case Town when '' then '' else ', ' + Town end +
Case County when '' then '' else ', ' + County end +
Case Postcode when '' then '' else ', ' + Postcode +
Case Telephone when '' then '' else ', ' + Telephone
>From tblClient where ...

Robert Djabarov
SQL Server & UDB
Sr. SQL Server Administrator
Phone: (210) 913-3148
Pager: (210) 753-3148
9800 Fredericksburg Rd. San Antonio, TX 78288
www.usaa.com

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of paul.hartland at fsmail.net
Sent: Monday, February 16, 2004 3:03 AM
To: SQL Group
Cc: accessd; dba-vb
Subject: [dba-SQLServer] Concatenating Fields In SQL Stored Procedure Or SQLView

To all,

I have a table in SQL Server 7.0 called tblClient with the following fields of information:
BranchNo
BranchCode
ClientName
Addrs1
Addrs2
District
Town
County
Postcode
Telephone

I want the following fields ClientName, Addrs1, Addrs2, District, Town, County, Postcode to appear as one line separated by a comma like below

BranchNo BranchCode Address
9999029 B&Q9999029 ClientName, Addrs1, District, Town, Postcode

As you can se from the example Addrs2 and County are missing, this is because these are blank sometimes. I have tried a couple of ways but always end up with the following

BranchNo BranchCode Address
9999029 B&Q9999029 ClientName, Addrs1, , District, Town, , Postcode

As you can see where the missing fields are the comma's still appear. Any help on this will be gratefully received.

Paul Hartland
Freeserve AnyTime - HALF PRICE for the first 3 months - Save £7.50 a month 
www.freeserve.com/anytime
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com
Freeserve AnyTime - HALF PRICE for the first 3 months - Save £7.50 a month 
www.freeserve.com/anytime


More information about the dba-SQLServer mailing list