Concatenate multiple columns in SQL Server

Question

I have address information parsed into several columns and I'm trying to concatenate the columns into one with no leading or trailing spaces using a view. I have tried LTRIM and RTRIM but can't seem to figure out how to get rid of leading/trailing spaces.

I am using SQL Server 2008 R2. Below is a sample of how our data is stored. The Address column is the output I'm looking/hoping to get.

Thanks in advance!

HouseNo|PreDir|StName|SufType|SufDir|UnitType|UnitNo|City    |State|Zip  |Address|
123    |      |Main  |St     |      |        |      |Anytown |CA   |55555|123 Main St Anytown, CA 55555
245    |      |3rd   |Ave    |NE    |        |      |Anytown |CA   |55555|245 3rd Ave NE Anytown, CA 55555
4675   |W     |Elm   |Dr     |      |        |      |Anytown |CA   |55555|4675 W Elm Dr Anytown, CA 55555
789    |E     |1st   |St     |      |Apt     |5     |Anytown |CA   |55555|789 E 1st St Apt 5 Anytown, CA 55555
432    |      |Locust|Blvd   |SE    |        |      |Anytown |CA   |55555|432 Locust Blvd SE Anytown, CA 55555

Show the SQL you're currently using and a create script for the involved tables. If those are varchar columns, there's no reason there should be any padding. — dodexahedron
– dodexahedron, Commented Apr 17, 2014 at 0:17
I am familiar with the '+' operator. My concern is how to get fields that are <null> or with an empty string to not show if empty. — user3543337
– user3543337, Commented Apr 17, 2014 at 0:37
Ok. Answer modified with an example of how to do that. Note that you must SET CONCAT_NULL_YIELDS_NULL ON for this to work, so that ' ' + ColName results in a null. It is usually off by default. — dodexahedron
– dodexahedron, Commented Apr 17, 2014 at 0:42
Appreciate the help, will try tomorrow! Is the CONCAT_NULL_YIELDS_NULL a setting within ssms or does it have to be run within the SQL statement? — user3543337
– user3543337, Commented Apr 17, 2014 at 1:01
It is a setting of the database itself, which is used for the context of incoming connections. You have to explicitly call that statement yourself, if it is off, for each connection you make, as it is only persistent in the context of the connection it was executed on. — dodexahedron
– dodexahedron, Commented Apr 17, 2014 at 1:11

dodexahedron · Accepted Answer · 2014-04-17 00:46:49Z

1

What you want is a combination of string concatenation and the COALESCE operator. COALESCE returns the first non-null value of a comma-separated list of expressions.

Example:

SET CONCAT_NULL_YIELDS_NULL ON;
SELECT HouseNo + COALESCE(' ' + PreDir, '') + COALESCE(' ' + StName, '') AS Address FROM YourTable

will result in:

Address 123 Main 245 3rd 4675 W Elm 789 E 1st 432 Locust

Note that the above requires CONCAT_NULL_YIELDS_NULL to be ON. If you are unsure if it is on for your connection, call SET CONCAT_NULL_YIELDS_NULL ON before the SELECT. CONCAT_NULL_YIELDS_NULL is generally off by default unless it has been set otherwise for your database (rare).

With CONCAT_NULL_YIELDS_NULL ON, adding ' ' + PreDir will result in NULL for records that have NULL in the PreDir column, which will then result in the COALESCE using the next value, '', which is an empty string and thus nothing.

edited Apr 17, 2014 at 0:46

answered Apr 17, 2014 at 0:19

dodexahedron

4,6832 gold badges32 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Concatenate multiple columns in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related