Remove extra spacing, line breaks, tabs from field

Question

I am attempting to pull contact data which includes basic client contact information. Problem: There are line breaks in the address field, and I cannot for the life of me figure out what will remove them.

I have tried variations of both the below queries and all produce the same results which are addresses that take up multiple lines when transferred to excel. I keep ending up with uneven rows of data--10 client names, but say 15 lines of addresses that don't line up with the client name.

Example 1:

SELECT clientname, contaddress, ContTownCity, ContCounty, contpostcode, Contphone, ContEmail, replace(
        replace(
    replace(
   replace(contAddress,
      Char (9),''),
       Char(13),''),
         char(10),'')              
FROM tblengagement as E     
INNER JOIN tblcontacts as C       
ON e.contindex = c.ContIndex;

Example 2 query from a view of the desired fields:

    select  replace(
          replace(
             replace(
                LTrim(RTrim(ContAddress)), 
             '  ',' |'),                    
          '| ',''),                         
       '|','')                              
       AS AddressSingleSpace
     from salesforce1

Ideas of a better way to query this or should I take a whole different approach to gathering this?

Probably a duplicate: http://stackoverflow.com/questions/14211346/how-to-remove-white-space-characters-from-a-string-in-sql-server — vercelli, Aug 10 '16 at 14:53

Dheebs · Accepted Answer · 2016-08-10T16:02:54.213

1

I would say you should just clean the data

Update Table SET Column = replace(replace(replace(column,Char(13),'',Char(10),''),Char(9),'')
WHERE Column is Not Null

*Change the column to a string containing these characters first before running though to verify. I haven't been able to test

The benefits of this is that it is better to clean the data properly then trying to do it in a select statements. Won't bog down your reports if you ever export to other systems like ssrs.

A another idea you can do is add a column that stores a date time which auto populates a null value. If you use the above script you can also get it to filter where the DateColumn is NULL and then get it to populate that column with a datestamp like GetDate().

It means that it wont take forever to run as the dataset grows.

edited Aug 10 '16 at 16:02

answered Aug 10 '16 at 14:59

Dheebs

398
1
6
19

was missing one extra close parentheses after Char (13), but this worked perfect. I will just tested this and it worked flawlessly on my backup DB. – Megan Aug 10 '16 at 15:48
Glad to hear :D I guess i shouldn't sit in bed coding at 2 am. missing them parentheses. I would say make a backup of your PROD system and then let it rip I might make a minor mod to make it run faster for you bare with me – Dheebs Aug 10 '16 at 15:59

Remove extra spacing, line breaks, tabs from field

1 Answers1