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,069 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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now