Solved

MS SQL Statement Result contains line breaks or white spaces :: How to TRIM ALL & ANY White Spaces???

Posted on 2007-11-29
5
1,088 Views
Last Modified: 2012-08-13
Dear Experts

I have a problem, my SQL 2005 db tables contains fields which in return contain whitespaces (hidden characters like #13#10, line breaks etc....) and this results in headaches when you want to do standard select query with where clauses etc because the data never matches.

How can I select fields and trim or remove all whitespaces of any nature?
A normal RTRIM() and LTRIM() does not seem to remove all.

Advise me please...
Thanks!
0
Comment
Question by:Marius0188
  • 3
  • 2
5 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20372136
You need to manually find those fileds and rename the fields using 'sp_Rename' statement
Once you finishes the modifications, you need to make changes in the stored procedures and the other codes
This example renames the contact title column in the customers table to title.
EXEC sp_rename 'customers.[contact title]', 'title', 'COLUMN'
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20372139
replace(replace( fieldname, Char(13), '' ), char(10), '')
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20372154
this script will return the statements to rename a column which contains a space in its name , run this, copy and paste the results in a new query window and run it


SELECT 'Exec sp_Rename '''+Table_Name+'.['+Column_Name+']'','''+REPLACE(Column_Name,' ','')+''',''COLUMN'''
 FROM information_Schema.columns WHERE Column_Name LIKE '% %'
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20372156
ooops , Do u really want to rename the fields or just want to replace those characters from the result set ?
0
 
LVL 25

Accepted Solution

by:
imitchie earned 500 total points
ID: 20372198
actually, this is probably better. run this on a table to 'clean' a field. it turns
#13#10 -> #13 (only)
#13 -> #10
#10 -> single space
then LTrim and RTrim takes care of leading and trailing bits. obviously you can use the same pattern in selects, and the REPLACE is your friend for turning double-spaces to single etc

update tbl set field = ltrim(rtrim(replace( replace(replace(field, char(13)+char(10), char(13)), char(13), char(10)), char(10), ' ')))
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Creating and Managing Databases with phpMyAdmin in cPanel.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question