CODEWITHSUNDEEP
sqlsql-server-2008t-sql
PriceCheaperton
PriceCheaperton

Reputation: 5349

Removing carriage returns using SQL

I have the following SQL which concatenates my address fields into ADDRESSLINE1:

SELECT Isnull(ADDRESSLINE1 + Char(13)+Char(10), '')
       + Isnull(ADDRESSLINE2 + Char(13)+Char(10), '')
       + Isnull(ADDRESSLINE3, '') AS ADDRESSLINE1

It returns:

ADDRESSLINE1       
------------------ -----------------------------------
10 TEST ROAD



(1 row(s) affected)

How do I amend my code so that if ADDRESSLINE2 AND ADDRESSLINE3 are blank '' then remove the unnecessary carriage returns.

I would expect:

ADDRESSLINE1       
------------------ -----------------------------------
10 TEST ROAD

(1 row(s) affected)

Upvotes: 0

Views: 391

Answers (1)

Bacon Bits
Bacon Bits

Reputation: 32170

Add the CR+LF to each line, then use the REVERSE(STUFF(REVERSE())) method to remove the last 2 characters.

SELECT REVERSE(STUFF(REVERSE(Isnull(ADDRESSLINE1 + Char(13)+Char(10), '')
       + Isnull(ADDRESSLINE2 + Char(13)+Char(10), '')
       + Isnull(ADDRESSLINE3 + Char(13)+Char(10), '')),1,2,'')) AS ADDRESSLINE1

Try this if you've got empty strings in your fields:

SELECT REVERSE(STUFF(REVERSE(Isnull(NULLIF(ADDRESSLINE1,'') + Char(13)+Char(10), '')
       + Isnull(NULLIF(ADDRESSLINE2,'') + Char(13)+Char(10), '')
       + Isnull(NULLIF(ADDRESSLINE3,'') + Char(13)+Char(10), '')),1,2,'')) AS ADDRESSLINE1

Upvotes: 1

Related Questions