Megan Megan - 1 year ago 64
SQL Question

Remove extra spacing, line breaks, tabs from field

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(
Char (9),''),
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(
' ',' |'),
'| ',''),
AS AddressSingleSpace
from salesforce1

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

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download