[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1123
  • Last Modified:

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

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
Marius0188
Asked:
Marius0188
  • 3
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
imitchieCommented:
replace(replace( fieldname, Char(13), '' ), char(10), '')
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
ooops , Do u really want to rename the fields or just want to replace those characters from the result set ?
0
 
imitchieCommented:
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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now