Remove extra spaces and CR

Posted on 2007-07-25
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
    LVL 50

    Accepted Solution

    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

    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

    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

    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

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now