Sql: update to replace varchar column by pattern

sfun28
sfun28 used Ask the Experts™
on
Folks,

I've got a column of varchar data that's populated with standard sql statements.  so the table might look like this:
Statement
-------------
SELECT X FROM Y where [abc]='def' and [ghi]='asdf'
INSERT INTO J (abc,def) Values (1,'aced')
....

I want to update the Statement column so that all values within single quotes are replaced with empty string.  so in the example above the result would be:

Statment
-----------
SELECT X FROM Y where [abc]='' and [ghi]=''
INSERT INTO J (abc,def) Values (1,'')

I don't know a prior how many single quotes there are, but I do know that they will always be balanced.

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2011

Commented:
could the string itself contain quotes?

Author

Commented:
yes.  I implimented RegExReplace:
http://forums.devshed.com/ms-sql-development-95/sql-regex-replace-how-to-425646.html

so perhaps I just need to find the right RegEx for this?

Author

Commented:
although if the string itself contains quotes, for it to be a valid Sql statement it would have to be double-quoted.

so:

Select * from Bla where Abcd = 'my life''s good'
Top Expert 2011
Commented:
yes but you'd still want it to be reduced to only  '' rather than ''''

anyway  try this
create this function and then run the two update statements....


create function dbo.emptystring(@statement varchar(max))
returns varchar(max)
as
Begin
declare @i bigint , @j bigint,@found char(1)
set @i=0

set @j=1

declare @newstr varchar(max)

while @i < datalength(@statement)
begin
     set @i=@i+1 
     set @newstr=@newstr+substring(@statement,@i,1)
     if substring(@statement,@i,1) = quote
     begin
          select @i=@i+1,@found='n'
          while @found='n'
          begin
             if substring(@statement,@i,1) = quote
             begin
               select @newstr=@newstr+substring(@statement,@i,1) 
                     ,@found='y'
             end
             else 
             begin
               set @i=@i+1
             end
          end
    end
end
return(@newstr)
end;
go

declare @q2 char(2),@q4 char(4)

set @q2=''''''
set @q4=@q2+@q2


update yourtable
   set statement=dbo/emotydtring(Statement)
 where statement like '%''%'
update yourtable
   set statement=replace(statement,@q4.@q2)
where statement like '%'+@q4 +'%'

Open in new window

Author

Commented:
thanks!!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial