Converting multi-line query to single line

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Converting multi-line query to single line

Author

Topic

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-02-13 : 11:47:10

Is there a simple way to convert a query spanning multiple lines to a single line query?

Example


SELECT
STUFF
FROM
TABLE
WHERE
STUFF <> 'BADSTUFF'

CONVERTED TO


SELECT STUFF FROM TABLE WHERE STUFF <> 'BADSTUFF'

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-13 : 11:54:55

If you use Query Analyzer to develop your queries, you can format the code however you want.

Be One with the Optimizer
TG

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-02-13 : 13:34:23

...throw it into a text editor and then search/replace all the carriage returns?

Uhm.....WHY?

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-02-13 : 13:39:03

I'm passing queries to the Bulk Copy Program and want them to still be maintainable (ie readable) for the developer.

Kristen
Test

22859 Posts

Posted - 2006-02-13 : 13:53:13

"I'm passing queries to the Bulk Copy Program and want them to still be maintainable (ie readable) for the developer"

Write the query as an Sproc and call that instead?

If not then just replace the CR/LF with spaces as blindman said:

REPLACE(REPLACE(@MyQueryString, CHAR(13), ' '), CHAR(10), ' ')

Kristen

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-02-13 : 15:03:26

Thanks Blindman and Kristin! Works like a charm. Way better than my approach (though I did learn about the WHILE functionality)
Can you help me understand the REPLACE nested in another REPLACE? I can see it's replacing the carriage returns with spaces, but not clear exactly how. What is special about the CHAR(13) and CHAR(10)?


DECLARE @MYQUERYSTRING VARCHAR(1000)

SET @MYQUERYSTRING =
"
SELECT
STUFF
FROM
TABLE
WHERE
STUFF <> 'BADSTUFF'
"
DECLARE @ONELINER VARCHAR(1000)
SET @ONELINER = REPLACE(REPLACE(@MyQueryString, CHAR(13), ' '), CHAR(10), ' ')

PRINT @ONELINER

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-02-13 : 16:56:45

CHAR(13) and CHAR(10) are the ASCII codes for carriage returns and line breaks.

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-02-13 : 17:16:51

Ah, well that makes excellent sense, thanks B.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-14 : 00:59:53

Also your code must be

DECLARE @MYQUERYSTRING VARCHAR(1000)

SET @MYQUERYSTRING =
'
SELECT
STUFF
FROM
TABLE
WHERE
STUFF <> ''BADSTUFF''
'
DECLARE @ONELINER VARCHAR(1000)
SET @ONELINER = REPLACE(REPLACE(@MyQueryString, CHAR(13), ' '), CHAR(10), ' ')

PRINT @ONELINER

Madhivanan

Failing to plan is Planning to fail

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-02-14 : 10:18:11

Thanks mad, those pesky quotes...

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-02-14 : 10:21:22

This avoids the need for two single tics (note the double quotes):
DECLARE @MYQUERYSTRING VARCHAR(1000)

SET @MYQUERYSTRING =
"
SELECT
STUFF
FROM
TABLE
WHERE
STUFF <> 'BADSTUFF'
"
DECLARE @ONELINER VARCHAR(1000)
SET @ONELINER = REPLACE(REPLACE(@MyQueryString, CHAR(13), ' '), CHAR(10), ' ')

PRINT @ONELINER

Kristen
Test

22859 Posts

Posted - 2006-02-14 : 11:11:03

But beware of BoL!

"Character constants must be enclosed in single quotation marks (') or double quotation marks ("). Enclosing a character constant in single quotation marks is recommended. Enclosing a character constant in double quotation marks is sometimes not allowed when the QUOTED IDENTIFIER option is set to ON."

Do folk use double-quotes for text strings?

Kristen

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-02-14 : 11:13:59

Thanks Kristen!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-15 : 01:42:36

>>Do folk use double-quotes for text strings?

Only at Client Side

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-02-15 : 02:36:37

<Chuckle!>

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-02-15 : 16:15:24

Ouch, okay I'll stop using those "s

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-16 : 01:39:00

>>Ouch, okay I'll stop using those "s

If you used Query Analyser, I think you might have stopped it earlier

Madhivanan

Failing to plan is Planning to fail

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-02-16 : 08:59:41

I do use query analyser, not real clear on why double quotes are bad, I'll read up on it.

Kristen
Test

22859 Posts

Posted - 2006-02-16 : 09:59:22

"not real clear on why double quotes are bad"

Its a legacy thing, still supported, but not for all circumstances, and probably going to be deprecated sooner or later.

It seems to be permissible to have "MyColumnName" or 'MyColumnName', but you should use [MyColumnName].

Likewise it seems to be possible to use "MyStringData" or 'MyStringData' but you should only use the second style.

At least I think that's the case!

Kristen

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-02-16 : 11:24:14

Hey thanks Kristen! This shop is still using SQL Server 2000 so that explains a lot.

Kristen
Test

22859 Posts

Posted - 2006-02-16 : 12:12:55

"SQL Server 2000"

Actually that's pretty new!

Kristen

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources