Remove extra spaces and CR

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.
Who is Participating?
LowfatspreadConnect With a Mentor Commented:
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

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...
cory_boothAuthor Commented:
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
cory_boothAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.