Post reply

How to Find/Replace carriage returns in query analyzer

  • March 21, 2006 at 1:35 pm

    #112290

    Does anyone know how to include carriage returns, line feeds, tabs, etc. in the Find & Replace dialogue in Query Analyzer?  I could have sworn I've done this before but can't remember how.  It may have been another tool though...

    So long, and thanks for all the fish,

    shang
    atk.com

  • March 21, 2006 at 2:19 pm

    #627970

    CHAR(13), CHAR(10), CHAR(9)

    _____________
    Code for TallyGenerator

  • March 21, 2006 at 2:43 pm

    #627972

    That's not what I am after.  I want to find carriage returns, line feeds and tabs in the code of my stored procedure.

    For instance, if I have this code:

    CREATE TABLE MyTable

    (Col1 varchar(10) NULL,

    Col2 varchar(10) NULL,

    Col3 varchar(10) NULL,

    Col4 varchar(10) NULL)

    And after a find and replace, I want it to look like this instead:

    CREATE TABLE MyTable

    (Col1 varchar(10) NULL

     , Col2 varchar(10) NULL

     , Col3 varchar(10) NULL

     , Col4 varchar(10) NULL)

    The find field would have this in it: ", {CR}"

    The replace field would have this in it: "{CR}{Tab}, "

    I want to know what to put in the find and replace fields to replace {CR} & {Tab}.

    So long, and thanks for all the fish,

    shang
    atk.com

  • March 21, 2006 at 2:52 pm

    #627974

    Can't do it in query analyzer put your script in notepad or some other ascii text editor.

     

  • March 21, 2006 at 2:56 pm

    #627975

    I was afraid of that.  Thanks for the input.

    So long, and thanks for all the fish,

    shang
    atk.com

  • March 22, 2006 at 7:52 am

    #628098

    I don't know how you'd do it in Notepad, but I'm a big fan of TextPad because of its regular expression and multi-line replacement capability.  In TextPad the replacement would be:

    Find:      ,\n\s*

    Replace: \n,\t

    Download a trial version at http://www.textpad.com/download/index.html

  • March 22, 2006 at 8:19 am

    #628111

    If you're just trying to format your code a bit better you can check out this site.  It's been helpful to me in the past.

    http://www.wangz.net/cgi-bin/pp/gsqlparser/sqlpp/sqlformat.tpl

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • March 23, 2006 at 1:48 pm

    #628460

    Try the following.  It will do what you want for every stored procedure in the current database.  You will have to have Update permission on the syscomments table.

    update sys.syscomments
      set text = replace(text, ',' + char(13) + char(10), char(13) + char(10) + char(9) + ', ' )
      where id in (select object_id from sys.objects where type = 'P')

  • March 23, 2006 at 2:39 pm

    #628470

    Doing a direct update to syscomments will probably work, but ...

    There's a miniscule chance that the pattern you're searching for will be missed because it is split across two records.  You will also have a problem adding chars to any text field that is already contains 4000 chars.

    You'd be better off scripting all the stored procs, reformatting where necessary, and running the script to save the changes.  Then you could see the changes and maybe have another person check it before you commit it to the database.

    You could use TextPad, use that web link, or any other tool.  I believe Apex SQL Edit will beautify SQL scripts.  Try a free trial version.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply