Find And Replace Subselects
-
Hello,
I would like to find and replace all subselects in a selection query.
The subselects will be modified before reëntering the selection query.
select * from (select * from A Union select * from B) as c
-> find: select * from A where A.id = 4 Union select * from B
-> modify: "select * from A Union select * from B" to "select * from A where A.id = 4 Union select * from B"
-> replace: select * from (select * from A where A.id = 4 Union select * from B) as c
Does anybody knows how the do this in T-sql?
Thx
-
Dynamic SQL. Basically you build your query string at runtime and execute it via sp_ExecuteSQL or EXEC(). See if this helps: http://www.sommarskog.se/dynamic_sql.html
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Company Policy blocks the page
-
This almost work, containing a SP and a FUNCTION
There is still an error, anyone up for the challenge?
SP:
DECLARE @select VARCHAR(8000)
SET @select = 'select * from (select * from (select * from tblPersoon) as tblpersoon WHERE PersoonID > 0 ) as tblPersoon WHERE tblPersoon.PersoonID IN(select PersoonID2 from tblContact)'
DECLARE @begin INT
DECLARE @end INT
SET @begin = CHARINDEX('(SELECT', @select, 0)
IF @begin 0 AND @end > 0
BEGIN
DECLARE @countA INT
DECLARE @countB INT
DECLARE @ok BIT
DECLARE @temp VARCHAR(8000)
SET @ok = 0
WHILE @ok = 0
BEGIN
SET @countA = LEN(SUBSTRING(@select, @begin , @end - @begin + 1)) - LEN(RTRIM(REPLACE(SUBSTRING(@select, @begin, @end - @begin + 1), '(', '')))
SET @countB = LEN(SUBSTRING(@select, @begin , @end - @begin + 1)) - LEN(RTRIM(REPLACE(SUBSTRING(@select, @begin, @end - @begin + 1), ')', '')))
IF @countA = @countB
SET @ok = 1
ELSE
SET @end = CHARINDEX(')', @select, @end + 1)
END
SET @temp = SUBSTRING(@select, @begin, @end)
print @temp
IF CHARINDEX('(select', @temp, 0) > 0
print null--SET @temp = dbo.SplitSelect(@temp)
ELSE
SET @temp = dbo.AddFilterConditions(@temp, 'tblPersoon.Familienaam like ''D''''hae%''', '')
--print substring(@select, 0, @end)
--print @end
SET @select = SUBSTRING(@select, 0, @begin) + @temp + SUBSTRING(@select, @end, LEN(@select))
SET @begin = CHARINDEX('(SELECT', @select, @end + LEN(@temp)-1)
IF @begin < LEN(@select)
SET @begin = @begin + 1
SET @end = CHARINDEX(')', @select, @begin + LEN(@temp)-1)
END
FUNCTION:
CREATE FUNCTION dbo.AddFilterConditions
(
@orgSelect VARCHAR(8000),
@qrySource VARCHAR(8000),
@endSelect VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @selectBegin INT
DECLARE @selectEnd INT
DECLARE @select VARCHAR(8000)
DECLARE @return VARCHAR(8000)
SET @selectBegin = 0
SET @selectEnd = 0
SET @return = ''
SET @Orgselect = REPLACE(@Orgselect, CHAR(9), ' ')
SET @Orgselect = REPLACE(@Orgselect, CHAR(10), ' ')
SET @Orgselect = REPLACE(@Orgselect, CHAR(13), ' ')
SET @selectBegin = CHARINDEX('SELECT', @Orgselect, @selectBegin)
SET @selectEnd = CHARINDEX('UNION', @Orgselect, @selectBegin) - @selectBegin
IF @selectEnd 0 AND @selectEnd > 0
BEGIN
SET @select = SUBSTRING(@Orgselect, @selectBegin, @selectEnd)
SET @selectBegin = CHARINDEX('SELECT', @Orgselect, @selectBegin + @selectEnd)
SET @selectEnd = CHARINDEX('UNION', @Orgselect, @selectBegin) - @selectBegin
IF @selectEnd < 0
SET @selectEnd =LEN(SUBSTRING(@Orgselect, @selectBegin, LEN(@Orgselect)))
SET @select = REPLACE(@select, CHAR(13), ' ')
SET @qrySource = REPLACE(@qrySource, CHAR(13), ' ')
SET @endSelect = REPLACE(@endSelect, CHAR(13), ' ')
DECLARE @temp VARCHAR(8000)
DECLARE @qryBuild VARCHAR(8000)
DECLARE @tables VARCHAR(8000)
DECLARE @regex VARCHAR(1000)
SET @qryBuild = ''
SET @tables = ''
--SELECT TABLES
SET @temp = SUBSTRING(@select, CHARINDEX('FROM', @select, 0), LEN(@select))
DECLARE @begin INT
DECLARE @end INT
DECLARE @table VARCHAR(100)
SET @begin = CHARINDEX('tbl', @temp, 0)
SET @end = CHARINDEX(' ', @temp, @begin) - @begin
IF @end 0 AND @end > 0
BEGIN
SET @table = SUBSTRING(@temp, @begin, @end)
SET @regex = '[a-zA-Z ]{' + CAST(LEN(@table) as varchar) + '}'
IF dbo.FindRegularExpression(@table, @regex, 0) = 1
SET @tables = @tables + @table + ','
SET @begin = CHARINDEX('tbl', @temp, @begin + @end)
SET @end = CHARINDEX(' ', @temp, @begin) - @begin
IF @end 0 AND @end > 0
BEGIN
SET @table = SUBSTRING(SUBSTRING(@qrySource, @begin, @end), 0, CHARINDEX('.', SUBSTRING(@qrySource, @begin, @end), 0))
SET @paramEnd = CHARINDEX(',', @qrySource, @begin)
IF @paramEnd = 0
SET @param = SUBSTRING(@qrySource, @begin, LEN(@qrySource))
ELSE
SET @param = SUBSTRING(@qrySource, @begin, @paramEnd - 1)
SET @begin = CHARINDEX('tbl', @qrySource, @begin + @end)
SET @end = CHARINDEX(' ', @qrySource, @begin) - @begin
IF CHARINDEX('.', @table, 0) = 0
BEGIN
IF CHARINDEX(@table+',', @tables, 0) > 0
BEGIN
IF LEN(@qryBuild) = 0 AND CHARINDEX('WHERE', @select, 0) = 0
BEGIN
SET @qryBuild = ' WHERE '
SET @qryBuild = @qryBuild + @param
END
ELSE
SET @qryBuild = @qryBuild + ' AND ' + @qryBuild + @param
END
END
END
SET @return = @return + (@select + @qryBuild + @endSelect)
IF @selectEnd + @selectBegin >= LEN(@Orgselect)
SET @return = @return + ' UNION '
END
--SET RETURN PARAM
RETURN @return
END
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply