I have a SQL query outputting a column which contains data from emails and chart conversations with lot of spaces, special characters, how can I clean and make this data show up in one row so that I could save the table output into a CSV file. PS: the query output is in millions.
Asked
Active
Viewed 247 times
0
-
1SQL Server, and any database in general, probably isn't the best place to do this cleanup. I vote for handling this in your app layer. – Tim Biegeleisen May 22 '18 at 14:56
-
I agree, but i need to provide a data extract so any work around ? – ivric May 22 '18 at 15:03
-
Please provide the query – Daniel Marcus May 22 '18 at 15:14
-
@DanielMarcus its a simple select query with few joins, so i loaded the query output into a temp table, and I'm trying something like this to remove those blackspaces, tabs etc. WHILE 1 = 1 BEGIN UPDATE dbo.YourTable SET Column = Replace(Column, Substring(Column, PatIndex('%[^0-9.-]%', Column), 1), '') WHERE Column LIKE '%[^0-9.-]%' If @@RowCount = 0 BREAK; END; – ivric May 22 '18 at 15:36
-
You dont need to run a loop for this - also I'd throw in some ltrim rtrims too - its possible you also have some special characters in which case this might be tough - id do a search for sql hidden charachters if i was you – Daniel Marcus May 22 '18 at 15:37
