Remove values between single quotes.

Question

Remove values between single quotes.

simon_s

Ten Centuries

Points: 1358
More actions
February 28, 2022 at 6:20 pm

#3990644
Hi

So Ive got a SQL trace uploaded into a table and I 'm looking at the ad-hoc calls, I want to summarize them and remove any values from the calls e.g instead of
select id,jobcode from table where id = '1' and jobcode in ('s','l')
I want to be left with
select id,jobcode from table where id = '' and jobcode in ('')
I've tried adapting some code from @pietlinden ;
```
DECLARE @string VARCHAR(200) = "select id,jobcode from table where id = '1' and jobcode in ('s','l')"

DECLARE @StartPos INT, @EndPos INT;
WHILE CHARINDEX("'",@string,1)>0
BEGIN
 set @StartPos = CHARINDEX("'",@string,1);
 set @EndPos = 1+CHARINDEX("'",@String,1);
 PRINT 'StartPos = ' + CAST(@StartPos AS VARCHAR(2)) + ' EndPos = ' + CAST(@EndPos AS VARCHAR(2));
 SET @string = REPLACE(@string,SUBSTRING(@string,@StartPos,@EndPos-@StartPos),'');
END;
SET @string = REPLACE(@string,' ',' ');
select @string;
```
but the results are the opposite of what Id expect ;
select id,jobcode from table where id = 1 and jobcode in (s,l)
what am I doing wrong ?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101098 More actions · Answer 1

Rather tricky, but this works with the one example you posted. I believe it will work with others, but you'd need to post more sample data for me to test it with for me to be sure.

DECLARE @string VARCHAR(200) = 'select id,jobcode from table where id = ''1'' and jobcode in (''s'',''l'')'

DECLARE @StartPos INT, @EndPos INT;
SET @StartPos=0;
WHILE CHARINDEX('''',@string,@StartPos+1)>0
BEGIN
 set @StartPos = CHARINDEX('''',@string,@StartPos+1);
 IF @StartPos = 0
     BREAK;
 set @StartPos = @StartPos + 1
 set @EndPos =   CHARINDEX('''',@String,@StartPos)-1;
 --PRINT 'StartPos = ' + CAST(@StartPos AS VARCHAR(2)) + ' EndPos = ' + CAST(@EndPos AS VARCHAR(2));
 SET @string = STUFF(@string,@StartPos,@EndPos-@StartPos+1,'');
END;

WHILE CHARINDEX('('''',''''', @string) > 0
BEGIN
  SET @string = REPLACE(@string,'('''',''''', '(');
END /*WHILE*/
SET @string = REPLACE(@string, '()', '('''')')

PRINT @string;

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

The STRING_AGG() function, available in SQL Server 2017 and up, makes this a pretty easy task along with a very useful tool that originates from this very site. You could easily turn the code into an iTVF (Inline Table Valued Function) for some pretty nasty-fast performance. DO NOT USE STRING_SPLIT() for "on-prem" as it does NOT return an ordinal for the elements and so cannot guarantee the order of the elements during the reassembly of the string.

The key to the code is to make the realization that, unless there are words that contain single quotes, the items that appear between quotes will ALWAYS be "even numbered" in any post-split array of values. Once those are gone, you need to end the odd_numbered ones with two single quotes and Bob's your uncle. Replacing the string ',' (single quotes included) takes care of any adjacent pairs.

DECLARE @string VARCHAR(200) = 'select id,jobcode from table where id = ''1'' and jobcode in (''s'',''l'')'
;
 SELECT REPLACE(STRING_AGG(ds.Item,'''''') WITHIN GROUP (ORDER BY ds.ItemNumber) ,''',''','')
   FROM dbo.DelimitedSplit8K(@String,'''')ds
  WHERE ds.ItemNumber%2 = 1
;

You can get a copy of the DelimitedSplit8K function from the "Resources" section at the bottom of the following article.

https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%E2%80%9Ccsv-splitter%E2%80%9D-function

The code above doesn't have to be in a function. It could be in a planned GROUP BY if you have an ID or other unique column to key the reassembly using STRING_AGG() . In other words, it can be done without RBAR.

The only time you might run into an issue is with name like "O'Brian", contractions like "don't", possessive words like "Jeff's" , or anything else that might make use of a single quote. You can pre-check strings for an even number of quotes using the likes of the following code. If it returns "0", then it at least has an even number of quotes. Of course, that wont work if you have two contractions (for example) in the same string.

 SELECT (LEN(@String) - LEN(REPLACE(@String,'''','')))%2

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

simon_s Ten Centuries Points: 1358 More actions · Answer 3

simon_s

Ten Centuries

Points: 1358

March 1, 2022 at 1:01 pm

#3991079

thank you both much appreciated