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.
SELECT clientname, contaddress, ContTownCity, ContCounty, contpostcode, Contphone, ContEmail, replace(
FROM tblengagement as E
INNER JOIN tblcontacts as C
ON e.contindex = c.ContIndex;
' ',' |'),
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.