T-SQL help

+2 Emmanuel Jones · December 15, 2014
I am trying to create a proc that will manipulate strings

I have a string that looks like


declare @where varchar(30000) = ' and a = ''2'' and b = ''blob'' and c = ''charlie 2324''';




I want to write code that will allow my to filter out any string combination within the @where variable. the ' a = ', ' b = ', and 'c = ' will stay the same but the values could be different.


this is how the proc will look



create proc changeSQL
@where varchar(3000)
as

--This is where I need to take out values I dont need
set @where = replace(@where, ' and a =  %_%', 'something else')

declare @sql varchar(max) = 'select * from db where g is not null' + @where

exec (@sql)

go

Post a Reply

Replies

Oldest  Newest  Rating
0 Bill Harding · January 24, 2015
You wouldn't do this in real life.

but whatever



DECLARE @INPUT AS  VARCHAR(3000) = ' stuff at the begining a=''123'' and b=''222333'' and c=''asasdasdx'' stuff at the the end'
DECLARE @START AS INT
DECLARE @END AS INT
DECLARE @OUTPUT AS VARCHAR(3000) =''

WHILE ( PatIndex('%''%''%',@input) >0)  --only loop while there is a pair of quotes
BEGIN 

--find starting double quote
SET @start = PATINDEX('%''%''%',@input) + 1

--add to output 
SET  @output= @output + left(@input,@start -1)

--chop from input
SET @input = SUBSTRING(@input,@start, LEN(@input))


--find end double quote
SET @end= PATINDEX('%''%',@input) + 1

--add to output
SET  @output= @output + ''''

--chop from input
SET @input = SUBSTRING(@input,@end, LEN(@input))

END

--the leftovers
SET @output = @output + @input

--cleaned, now insert what you want
SET @output  = REPLACE(@output, 'a=''''', 'a=''something else for a''' )
SET @output  = REPLACE(@output, 'b=''''', 'a=''something else for b''' )
SET @output  = REPLACE(@output, 'c=''''', 'a=''something else for c''' )

SELECT @output
  • 1

SQL & Databases

103,764 followers
About

Everything SQL and Databases related in here!

Links
Moderators
Bucky Roberts Administrator