Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2007-11-29
5
Medium Priority
?
1,121 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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

824 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