473,326 Members | 2,081 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Function to format SQL

Don't suppose anyone out there has got a function that will take an
unformatted SQL query and return a nicely formatted string (with embedded
tabs and carriage returns). The best I've got so far is:

SELECT VIEWNAME, VIEWSCHEMA,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(VARCHAR(Vi ews.Text,3000),',
',CHR(10) || ',' || CHR(9)),'SELECT', CHR(10) || 'SELECT' || CHR(10) ||
CHR(9)),'UNION',CHR(10) || 'UNION'),'WHERE',CHR(10) || 'WHERE' ||
CHR(9) ),'FROM',CHR(10) || 'FROM' || CHR(10) || CHR(9))
FROM SYSCAT.VIEWS Views
;

Any better offers would be appreciated.

Regards
Paul Vernon
Business Intelligence, IBM Global Services
Nov 12 '05 #1
3 9035
aka
instead of replace(replace(... you could try a look at the translate scalar
function
"Paul Vernon" schrieb im Newsbeitrag
news:ca***********@gazette.almaden.ibm.com...
Don't suppose anyone out there has got a function that will take an
unformatted SQL query and return a nicely formatted string (with embedded
tabs and carriage returns). The best I've got so far is:

SELECT VIEWNAME, VIEWSCHEMA,
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(VARCHAR(Vi ews.Text,3000),',
',CHR(10) || ',' || CHR(9)),'SELECT', CHR(10) || 'SELECT' || CHR(10) ||
CHR(9)),'UNION',CHR(10) || 'UNION'),'WHERE',CHR(10) || 'WHERE' ||
CHR(9) ),'FROM',CHR(10) || 'FROM' || CHR(10) || CHR(9))
FROM SYSCAT.VIEWS Views
;

Any better offers would be appreciated.

Regards
Paul Vernon
Business Intelligence, IBM Global Services

Nov 12 '05 #2
"aka" <ak*@aka-soft.de> wrote in message news:cb**********@online.de...
instead of replace(replace(... you could try a look at the translate scalar function


TRANSLATE replaces single characters with other single characters. I need
something just a little more powerful.

Regards
Paul Vernon
Business Intelligence, IBM Global Services
Nov 12 '05 #3
aka
http://www-106.ibm.com/developerwork...301stolze.html

I've successfully done this in my Stinger/Windows environment, the code
inside the udf would be a great place to do the formatting and one would
also have the regular expression api at hand for this...

Regards
aka
instead of replace(replace(... you could try a look at the translate

scalar
function


TRANSLATE replaces single characters with other single characters. I need
something just a little more powerful.

Regards
Paul Vernon
Business Intelligence, IBM Global Services

Nov 12 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Jean | last post by:
Hi, I have a query that contains the following in the WHERE part of the query: WHERE....Format(IIf(nz()<>0,, IIf(nz()<>0,, IIf(nz()<>0,, ))),"0.0") AS Körperhöhe,....
6
by: Stuart McGraw | last post by:
I am looking for a VBA "format" or "template" function, that is, a function that takes a format string and a varying number of arguments, and substitutes the argument values into the format string...
1
by: Ian Pilcher | last post by:
Can va_copy be used to copy a va_list object that was initialized in a different function? (I.e. the function in which va_copy is used is called by the function in which the original va_list is...
13
by: Roy Hills | last post by:
I've seen two different function prototype formats used for ANSI C, and I'm unsure as to which is the correct or preferred one. 1st Format (this is what I use) type function(type, type, type);...
2
by: Tobias Olbort | last post by:
Hello, i've a outer function, which takes a params-array as a parameter. I want to pass this array to inner function with a params-array (e. g. string.format). When i've passed an integer to...
4
by: Matt | last post by:
I'm trying to write a shared function that will convert all my phone numbers from format 1234567890 to (123) 456-7890. Here is my function that I wrote: Public Shared Function...
3
by: scorpion53061 | last post by:
Could you look at this function and tell me why I am getting an exception concerning date cast on line set apart by stars... If you have better suggestions of how to do this I would be open to...
4
by: Paul | last post by:
Anyone have code that emulates the Nz function in Microsoft Access? In Access it is: Nz(Value as variant, Optional ValueIfNull as Variant) as Variant
4
by: OzNet | last post by:
I have some functions to calculate the working days in a given period. This includes a table that is queried to calculate the number of public holidays that don’t occur on a weekend. If I test...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.