I often end up with a variety of data where strings are hard coded with values, but the queries are really the same under the sheets. This means I have a hard time really determining how many unique queries I really have.

This function will flatten out the hard coded values into ‘xxx’… thus allowing me to compare like strings.

create table tempdb..denorm(strMixed varchar(8000))

insert into tempdb..denorm values ('select * from tblContacts where Id = ''123'' and type = 12')
insert into tempdb..denorm values ('select * from tblContacts where Id = ''234'' and type = 12')
insert into tempdb..denorm values ('select * from tblContacts where Id = ''456'' and type = 12')

select strMixed from tempdb..denorm

select dbo.norm_string(strMixed) from tempdb..denorm

strMixed
--------------------------------------------------------------
select * from tblContacts where Id = '123' and type = 12
select * from tblContacts where Id = '234' and type = 12
select * from tblContacts where Id = '456' and type = 12

(3 row(s) affected)


--------------------------------------------------------------
select * from tblContacts where Id = 'xxx' and type = 12
select * from tblContacts where Id = 'xxx' and type = 12
select * from tblContacts where Id = 'xxx' and type = 12

(3 row(s) affected)

Here’s the code:

create function norm_string(@str varchar(8000))
returns varchar(8000)
as begin

declare @tblOffset table
(
position int identity,
offset int
)



declare @i bigint
declare @c char(1)
declare @str2 varchar(8000)
declare @str3 varchar(8000)

set @i = 1


while @i < (len(@str)+1)
begin

 set @c = substring(@str,@i,1)
 if @c = char(39)
 begin
 insert into @tblOffset(offset) values (@i)
 end

 set @i=@i+1

end

-- select * from @tblOffset

declare @positionprime int
declare @offsetprime int

declare @position int
declare @offset int
declare @offset1 int

declare @start int
declare @end int

declare @position2 int
declare @offset2 int
declare @stuff varchar(10)
declare @primingoffset int


set @stuff = 'xxx'


declare offsets_cursor cursor for 
select position, offset from @tblOffset

OPEN offsets_cursor

FETCH NEXT FROM offsets_cursor INTO @position, @primingoffset

-- first, get the head of the string. read from beginning to the first delimeter.

set @str2=substring(@str,1,@primingoffset)+@stuff


FETCH NEXT FROM offsets_cursor INTO @position, @offset1
FETCH NEXT FROM offsets_cursor INTO @position2, @offset2



WHILE @@FETCH_STATUS = 0
BEGIN


 set @str2 = @str2+substring(@str,@offset1,@offset2-@offset1)+char(39)+@stuff

 FETCH NEXT FROM offsets_cursor INTO @position, @offset1
 FETCH NEXT FROM offsets_cursor INTO @position2, @offset2


end

close offsets_cursor
deallocate offsets_cursor

-- now put the tail on


set @str2 = @str2+substring(@str,@offset1,len(@str)-@offset1+1)

return @str2
end