How to Find/Replace carriage returns in query analyzer

Question

Post reply

How to Find/Replace carriage returns in query analyzer

shang

Mr or Mrs. 500

Points: 528
More actions
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

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

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

Sergiy SSC Guru Points: 110208 More actions · Answer 1

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

_____________
Code for TallyGenerator

shang Mr or Mrs. 500 Points: 528 More actions · Answer 2

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

Ray M SSC-Insane Points: 21093 More actions · Answer 3

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

shang Mr or Mrs. 500 Points: 528 More actions · Answer 4

I was afraid of that. Thanks for the input.

So long, and thanks for all the fish,

shang
atk.com

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 5

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

Luke L SSC-Dedicated Points: 35547 More actions · Answer 6

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]

Greg Jennings SSC Enthusiast Points: 175 More actions · Answer 7

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')

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 8

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.