Learn how to a build a cloud-first strategyRegister Now


Remove extra spaces and CR

Posted on 2007-07-25
Medium Priority
Last Modified: 2012-05-05
Request assistance with correcting a database table after cut/paste and other import operations were done.  I have sporadic character returns and square characters everywhere.

Please look at the following:  www -dot- thebooths -dot- org/1.jpg for a better example.

I have been using the following script, but it doesn't seem to work very well.
UPDATE    SenarioCaseTbl
SET              TestResults = REPLACE(TestResults, CHAR(127), Char(13) + char(10))
I also have trouble identifying the square in particular.

anyone have any ideas how to fix this?

These particular tables are varchar and will not be used on a web platform.
Question by:cory_booth
  • 2
  • 2
LVL 50

Accepted Solution

Lowfatspread earned 2000 total points
ID: 19565484
the square could be one of several actual characters...

you'll need to check out a specific rows data...

declare @out varchar(8000),@i int,@out2 varchar(8000)

select @out = testresults ,@i=1
 from senariocasetbl
where id = 23

while @i <= @datalength(@out)
   if ascii(substring(@out,@i,1) not between  32 and 127
         print @i + ' ' + convert(char(3),ascii(substring)@out,@i,1)+' ' +substring(@out,@i,1)
  set @i=@i+1

UPDATE    SenarioCaseTbl
SET           TestResults = replace(replace(replace(REPLACE(TestResults,Char(13) + char(10),CHAR(127)),char(13),' '),char(10),' '),char(9),' ')
 Where testresults like '%' + char(13) +'%'
    or testresults like '%' +char(10)+'%'
    or testresults like '%' + char(9) + '%'    -- tab character

LVL 50

Expert Comment

ID: 19565512
you should have a trigger on the table to ensure that this invalid data is not allowed to be captured in the first place...
or to automatically perform  this type of correction...

Author Comment

ID: 19565576
thanks for the help but I am receiving an error tryintg to run the script in SQL

Server: Msg 137, Level 15, State 2, Line 7
Must declare the variable '@datalength'.
Server: Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'not'.
Server: Msg 128, Level 15, State 1, Line 11
The name 'substring' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

I am getting ready to try the replace SQL

Author Comment

ID: 19565618
Here are the results of the replace query....

www -dot - thebooths -dot- org/2.jpg

As you can see, still have the box issues and I lost my CR/LF (shift enter) line wrap

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

810 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