July 7, 2016 at 9:39 am
Ok well my head may well explode soon and I have exhausted the usually channels of my own (limited) brain power and google.
Im trying to aggregate a sql trace file obtained via profiler... , Id say 80% of it is fine but those pesky criteria in select statements is getting in the way of accurate results e.g
instead of multiple rows of
select * from client where id = '789546' and county = 'Northants'
select * from client where id = '789456' and county = 'Essex'
I want
select * from client where id = '' and county = ''
So my question is how can I remove alphanumeric data from between quotes , where a single column may contain data like the example above with multiple instances of single quotes ?
any words of wisdom gratefully received !
July 7, 2016 at 9:46 am
T_SQL isn't very good at string manipulation, as you've possibly noticed. The easiest way would be to use a Regular Expressions CLR - you should be able to find one on the internet fairly easily. Failing that, you're going to need to go through the string and use CHARINDEX to find the position of each instance of "'", and then plug those positions into STUFF to remove the text enclosed in the inverted commas.
John
July 7, 2016 at 12:26 pm
DECLARE @Inputs TABLE (RowID INT IDENTITY(1,1) PRIMARY KEY, TextString VARCHAR(100))
insert INTO @inputs
VALUES ('SELECT * from client where id = ''789546'' and county = ''Northants''')
,('select * from client where id = ''789456'' and county = ''Essex''')
select * from @inputs;
select i.RowID, cat.item
from @inputs i
cross apply (Selectstuff((SELECT ' ' +
CASE WHEN ds.item like '''%''' THEN '''''' else item end-- gets rid of the data between apostrophes
FROM dbo.DelimitedSplit100(TextString,' ') ds
ORDER BY ItemNumber
FOR XML PATH ('')
),1,1,'') as [item]) cat
Explanation:
1. DelimitedSplit8k is a commonly used string splitter, used here to break each line down into it's component strings as separated by spaces.
2. The CASE statement looks for strings that begin and end with apostrophes, then replaces them with ''.
3. The FOR XML PATH('') is a common technique in Microsoft SQL to concatenate rows into single strings.
Because the code looks for strings split by spaces, values such as 'New York City' will not be correctly handled by this technique. If this is the case another technique would be to use a recursive CTE as a looping technique to search and replace strings like PATINDEX '%''%""%' over and over.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply