fun w/ SQL

get_ElementSubstringByTag.example.sql

--
--microsoft sql server
--get element from string by tags, mostly useful for MSSQL versions lacking (prior to) a native xml-datatype


--
--common
CREATE FUNCTION get_ElementByTags ( @vXML varchar(max) , @vTag varchar(64) )
RETURNS varchar(512)
BEGIN
	BEGIN
		declare @xL varchar(2) , @xLE varchar(3) , @xR varchar(2) , @xRE varchar(3) , @iLbound int , @iRbound int
		declare @vToken varchar(32) , @vResult varchar(64)
		set @xL =	'<'
		set @xLE =	'</'
		set @xR =	'>'
		set @xRE =	'/>'
		set @iLbound = charindex( @xL + @vToken + @xR , @vXML ) + LEN( @xL + @vToken + @xR) --:'left charnum'
		set @iRbound = charindex( @xLE + @vToken + @xR , @vXML                             --:'right charnum'
		set @vResult = substring( @vXML, @iLbound , abs(@iRbound - @iLbound) )
		RETURN @vResult 
	END	
END

/*
OUTPUT :: CREATE FUNCTION permission denied in database :(
*/

lahman591

--
--
--(postrgesql) lahman591


--
--biggest record improvements year over year
lahman591=# SELECT teams.name ,teams.yearid ,teams.w       
        ,CAST( teams.w as double precision ) / cast( teams.g as double precision)                                                                       
        ,cast( teams.w as double precision) / cast( teams.g as double precision) - cast( COALESCE( lookback.w ,teams.w ) as double precision) / cast( lookback.g as double precision)                                               
FROM teams                                                                  
INNER JOIN (SELECT * FROM teams) AS lookback                                
        ON teams.yearid = (lookback.yearid + 1)                             
        AND teams.teamid = lookback.teamid                                  
WHERE teams.yearid > 1970                                                   
ORDER BY 6 desc                                                             
LIM