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,104 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL 2014 missing dll from Bin? 3 32
SQL Syntax 6 41
Specify timing interval fro change data 2 54
Append Query Access 2010 4 23
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

756 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