Function to Format Select Queries to Columns

,

This function will strip all extra spaces, CRLFs and tabs from a SELECT statement, from the SELECT keyword to the End of the Order By clause, then reformat the statement to put all keywords at the left margin and subordinate clauses indented by ONE Tab.  There is an example of a SELECT statement that came from the Enterprise Manager Query Builder Grid, with the Final output shown after that.  The function is invoked with:  print dbo.ufx_fixcommas(' ') with the Select statement between the single quotes.  Any single quoted parameters MUST have TWO single quotes before running the Print command.  The function strips the double quotes and leave the single quote in place.  I usually use the REPLACE function first to replace ' with '' then add the print dbo.ufx_fixcommas(' before the SELECT keyword then close the SELECT statement with ')  This is not as fancy as I would like, but it's a lot quicker than manually formatting SQL statements.

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

--* -------------------------------------------------------------
--* NAME: --print dbo.ufx_fixcommas('
--* AUTHOR: Butch Hoffman  (hoffman@gatewaycc.edu)
--* DATE: 09/29/2003
--* APPLICATION: ODS Programmer
--* PURPOSE: Convert SQL to Column format
--* ORGANIZATION: GateWay Community College - Phoenix AZ
--* ---------------------------------------------------------------

CREATE FUNCTION dbo.ufx_FixCommas (
	@InputStr00 varchar (8000)
) 
RETURNS varchar(8000) AS  
BEGIN
Declare
	@InputStr1 varchar (8000),
	@InputStr0 varchar (80),
	@InputStr varchar (8000),
	@NewStart int,
	@NewStart1 int,
	@OldLen int,
	@CrLf char (2),
	@Tab char (1)
SET @CrLf = char(13) + char(10)
SET @Tab = char(9)
-- The next 7 lines strip spaces, tabs and CrLf's from the input string.
set @InputStr00 =  REPLACE ( @InputStr00 , @CrLf , ' ' )
set @InputStr00 =  REPLACE ( @InputStr00 , @Tab , ' ' )
set @InputStr00 =  REPLACE ( @InputStr00 , '  ' , ' ' )
set @InputStr00 =  REPLACE ( @InputStr00 , '  ' , ' ' )
set @InputStr00 =  REPLACE ( @InputStr00 , '  ' , ' ' )
set @InputStr00 =  REPLACE ( @InputStr00 , '  ' , ' ' )
set @InputStr00 =  LTrim(Rtrim(@InputStr00))
--print @InputStr00
set @InputStr = @InputStr00
--set @InputStr = 'SELECT [AA_INST], [AA_SID], [AA_NAME], [AA_PREV_NAME], [AA_ALT_INST], [AA_NAME_SORT], [AA_SPEC_NAME_FLAG], [AA_SALUTATION], [AA_PREV_SID], [AA_BIRTH_DT], [AA_SEX], [AA_MARITAL_STAT], [AA_ETHNIC_ORIGIN], [AA_RELIGION], [AA_CITIZENSHIP], [AA_STATE_ORGIN], [AA_COUNTY_ORGIN], [AA_RESIDENCY], [AA_INFO_RELSE_FLAG], [AA_HANDICAP_TYPE], [AA_VISA_TYPE], [AA_VISA_EXP_DT], [AA_FOREIGN_STUDENT], [AA_CO_OP_STUDENT], [AA_VET_CODE], [AA_VET_BENEFIT], [AA_VET_CLAIM_NUM], [AA_VET_BEN_EXP_DT], [AA_GI_CERT_TERM], [AA_GI_CERT_TYPE], [AA_NOK_NAME], [AA_NOK_RELATION], [AA_NOK_PHONE], [AA_ENTRY_DT], [AA_TONGUE], [AA_T2202A_ELIG], [AA_CURSTAT], [AA_LAST_RPT_YR], [AA_LAST_FULL_PART], [AA_LAST_LVL], [AA_LAST_GRAD_DEG], [AA_LAST_GRAD_MAJ], [AA_FEECAT], [AA_FORPOS], [AA_IMSTAT], [AA_BIRTH_CENTURY_FILLER], [AA_SECURITY_GROUP], [AA_PERSONAL_ID], [AA_NCAA_ATHLETE], [AA_NCAA_GPA], [AA_PASSPORT_EXP_DT], [AA_STU_WORK_PHONE], [AA_NICKNAME], [AA_DRAFT_REG], [AA_HOLD_FLAGS], [AA_MM_IND], [AA_BR_IND], [AA_RA_IND], [AA_RB_IND], [AA_RP_IND], [AA_SL_IND], [AA_SITE], [AA_EXCURR_FILLER], [AA_SPOUSE_ID], [AA_ADS_RECORDED], [AA_BD_ARCHIVED_TERM], [AA_SSN], [AA_UID], [AA_UID_ACTION], [AA_UID_ACTION_DATE], [AA_ABILITY_TO_BENEFIT], [AA_HANDICAP_1], [AA_HANDICAP_2], [AA_HANDICAP_3], [AA_ADMISSION], [AA_APP_COMPLETE], [AA_CURR_INTENT], [AA_ELIGIBLE], [AA_ENTRY_YYT], [AA_ENTRY_DAYEVE], [AA_HS_CODE], [AA_HS_YEAR], [AA_HS_GRADUATE], [AA_CUM_MAJ_HOURS], [AA_SSCLAIMNUM], [AA_SKILL_CENTER], [AA_FAMS_CHECKS_RULE], [AA_ORIGIN_SCREEN], [AA_ORIGIN_OPERID], [AA_ORIGIN_DT], [AA_PP_IND], [AA_MAPS_IND], [AA_WRK_PHN_EXT], [AA_APPL_REC], [AA_ENG_PRIM_LANG], [AA_ENG_PL_ASSIST], [AA_HW_ITEMS], [AA_HELP_WITH_OTHER], [AA_FIRST_LANG], [AA_CURR_LANG], [AA_OTHER_LANG_1], [AA_OTHER_LANG_2], [AA_OTHER_LANG_3], [AA_KIOSK_PIN], [AA_BAR_CODE], [AA_ADDRESS_CTR], [AA_MAINT_DT]'
set @OldLen = Len(@InputStr)
set @InputStr1 = ''
set @InputStr0 =
	SUBSTRING(@InputStr,1,PATINDEX('% %',@InputStr))
set @NewStart = PATINDEX( '% %' , @InputStr0 )
set @NewStart1 = PATINDEX( '% %' , @InputStr0 )
while @NewStart1 < Len(@InputStr)
Begin
If @InputStr0 = 'SELECT'
Begin
set @InputStr1 = @InputStr1 + RTrim(@InputStr0)
End
Else
If @InputStr0 = 'DISTINCT'or @InputStr0 = 'join'
Begin
set @InputStr1 = @InputStr1 +' '+ RTrim(@InputStr0)
End

Else
If @InputStr0 = '+' or @InputStr0 = 'AS' or @InputStr0 = '='
 or @InputStr0 = '<>' or @InputStr0 = '>=' or @InputStr0 = '<='
 or @InputStr0 = '<' or @InputStr0 = '>' or @InputStr0 = '/'
 or @InputStr0 = 'by' or @InputStr0 = 'like' or @InputStr0 = '*'
Begin
set @InputStr1 = @InputStr1 + ' ' + RTrim(@InputStr0)
End

Else
If  @InputStr0 = 'From' or @InputStr0 = 'inner' or @InputStr0 = 'where'
 or @InputStr0 = 'order' or @InputStr0 = 'on' or @InputStr0 = 'and'
 or @InputStr0 = 'BETWEEN' or @InputStr0 = 'OR' 
Begin
set @InputStr1 = @InputStr1 + @CrLf + RTrim(@InputStr0)
End

Else
Begin
set @InputStr1 = @InputStr1 + @CrLf + @Tab + RTrim(LTrim(@InputStr0))
End
set @NewStart =  PATINDEX('% %', @InputStr ) + 1

IF PATINDEX('% %',@InputStr)+1 = 1
Begin
	set @NewStart1 = @OldLen + 2
End
set @InputStr = LTrim(SUBSTRING(@InputStr,@NewStart,@OldLen))
set @InputStr0 =
	SUBSTRING(@InputStr,1,PATINDEX('% %',@InputStr))
set @InputStr0 = LTrim(@InputStr0)
End
set @InputStr1 = RTrim(@InputStr1) + LTrim(@InputStr)
Return @InputStr1
End

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
-- End of Function
/*
-- This is one query in need of formating.  Note the TWO single quotes ('')
-- around the Hard Coded Parameters. This function strips the TWO back to ONE '.
-- I usually use the Replace function to put the two single quotes in, then
-- add the "print dbo.ufx_fixcommas('" before the SELECT key word,
-- and add the closing ') at the end of the Order By clause.
-- This isn't as perfect as I'd like it, but it generally gets the key words
-- left justified and subordiante clauses are indented by ONE tab.
*/
/*
print dbo.ufx_fixcommas('SELECT DISTINCT 
                      rc.RC_CRS_PREFIX + rc.RC_CRS_NUMBER + rc.RC_SUFFIX_1 + rc.RC_SUFFIX_2 AS Course, rc.RC_SECTION, rc.RC_SECTION_TITLE, 
                      rcm.RC_SD_BLDG, rcm.RC_SD_ROOM, rc.RC_TALLY_ENR, rcsd.RC_INSTR_NAME, rcm.RC_SD_START_TIME, rcm.RC_SD_STOP_TIME, 
                      rcm.RC_SD_DAYS, rc.RC_START_DT, rc.RC_END_DT, rc.RC_DAY_EVE
FROM         GW_RCFILE rc INNER JOIN
                      GW_RC_SD_MEETS rcm ON 
                      rc.RC_CRS_PREFIX + rc.RC_CRS_NUMBER + rc.RC_SUFFIX_1 + rc.RC_SUFFIX_2 + rc.RC_SECTION = rcm.RC_CRS_PREFIX + rcm.RC_CRS_NUMBER +
                       rcm.RC_SUFFIX_1 + rcm.RC_SUFFIX_2 + rcm.RC_SECTION INNER JOIN
                      GW_RC_SD_INSTRUCTORS rcsd ON 
                      rc.RC_CRS_PREFIX + rc.RC_CRS_NUMBER + rc.RC_SUFFIX_1 + rc.RC_SUFFIX_2 + rc.RC_SECTION = rcsd.RC_CRS_PREFIX + rcsd.RC_CRS_NUMBER +
                       rcsd.RC_SUFFIX_1 + rcsd.RC_SUFFIX_2 + rcsd.RC_SECTION
WHERE     (rcm.RC_SD_START_TIME <> ''TBA'') AND (rc.RC_START_DT BETWEEN @what_start AND @what_end) AND (rc.RC_DAY_EVE = @what_de) AND 
                      (rcm.RC_CTF_TERM = @what_term) AND (rcsd.RC_CTF_TERM = @what_term) AND (rc.RC_REG_STATUS <> ''X'') AND (rc.RC_ON_CAMPUS = ''Y'') AND 
                      (rcm.RC_SD_SEQUENCE_COUNTER = ''0'') AND (rcsd.RC_SD_INSTR_SEQUENCE_COUNTER = ''0'')
ORDER BY Course')
*/

-- Formatted output Below:
/*
SELECT DISTINCT
	rc.RC_CRS_PREFIX +
	rc.RC_CRS_NUMBER +
	rc.RC_SUFFIX_1 +
	rc.RC_SUFFIX_2 AS
	Course,
	rc.RC_SECTION,
	rc.RC_SECTION_TITLE,
	rcm.RC_SD_BLDG,
	rcm.RC_SD_ROOM,
	rc.RC_TALLY_ENR,
	rcsd.RC_INSTR_NAME,
	rcm.RC_SD_START_TIME,
	rcm.RC_SD_STOP_TIME,
	rcm.RC_SD_DAYS,
	rc.RC_START_DT,
	rc.RC_END_DT,
	rc.RC_DAY_EVE
FROM
	GW_RCFILE
	rc
INNER JOIN
	GW_RC_SD_MEETS
	rcm
ON
	rc.RC_CRS_PREFIX +
	rc.RC_CRS_NUMBER +
	rc.RC_SUFFIX_1 +
	rc.RC_SUFFIX_2 +
	rc.RC_SECTION =
	rcm.RC_CRS_PREFIX +
	rcm.RC_CRS_NUMBER +
	rcm.RC_SUFFIX_1 +
	rcm.RC_SUFFIX_2 +
	rcm.RC_SECTION
INNER JOIN
	GW_RC_SD_INSTRUCTORS
	rcsd
ON
	rc.RC_CRS_PREFIX +
	rc.RC_CRS_NUMBER +
	rc.RC_SUFFIX_1 +
	rc.RC_SUFFIX_2 +
	rc.RC_SECTION =
	rcsd.RC_CRS_PREFIX +
	rcsd.RC_CRS_NUMBER +
	rcsd.RC_SUFFIX_1 +
	rcsd.RC_SUFFIX_2 +
	rcsd.RC_SECTION
WHERE
	(rcm.RC_SD_START_TIME <>
	'TBA')
AND
	(rc.RC_START_DT
BETWEEN
	@what_start
AND
	@what_end)
AND
	(rc.RC_DAY_EVE =
	@what_de)
AND
	(rcm.RC_CTF_TERM =
	@what_term)
AND
	(rcsd.RC_CTF_TERM =
	@what_term)
AND
	(rc.RC_REG_STATUS <>
	'X')
AND
	(rc.RC_ON_CAMPUS =
	'Y')
AND
	(rcm.RC_SD_SEQUENCE_COUNTER =
	'0')
AND
	(rcsd.RC_SD_INSTR_SEQUENCE_COUNTER =
	'0')
ORDER BY Course
*/

Rate

Share

Share

Rate