CODEWITHSUNDEEP
sqlsql-server
user3543337
user3543337

Reputation: 1

Concatenate multiple columns in SQL Server

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

Upvotes: 0

Views: 167

Answers (1)

dodexahedron
dodexahedron

Reputation: 4657

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.

Upvotes: 1

Related Questions