September 17, 2015 at 7:46 pm
Hi,
I have two tables in SQL Server--an expression table and a translate table.
DECLARE @EXPRTABLE TABLE
(
expression varchar(100)
)
INSERT INTO @EXPRTABLE
SELECT 'CALL("-","","TEST")'
UNION ALL
SELECT 'SUB(2,67,TEST,4,)'
DECLARE @TRANSLATETABLE TABLE
(
pattern varchar(100)
)
INSERT INTO @TRANSLATETABLE
SELECT 'CALL("#","#","#")'
UNION ALL
SELECT 'SUB(2,#,TEST,4,)'
SELECT *
FROM @EXPRTABLE
SELECT *
FROM @TRANSLATETABLE
I am trying to (for lack of a better way to explain it), overlay the pattern onto the expression and extract the text where the "#" symbols are.
So,
CALL("-","","TEST") using pattern CALL("#","#","#") would return -,,TEST
SUB(2,67,TEST,4,) using pattern SUB(2,#,TEST,4,) would return 67
Does anyone know how I might go about this?
Thanks,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
September 17, 2015 at 8:22 pm
If you just used "%" wildcard instead of "#" (or REPLACE it) and then did a LIKE I think that woudl match - but would that be a suitable solution?
September 17, 2015 at 8:31 pm
I might be able to use the wildcard character. Maybe I'm just over complicating a simple problem but how might you extract the values that aren't matched by the pattern using the %?
Mike Scalise, PMP
https://www.michaelscalise.com
September 17, 2015 at 9:14 pm
SELECTpattern, expression
FROM@EXPRTABLE
JOIN @TRANSLATETABLE
ON expression LIKE REPLACE(pattern, '#', '%')
SELECTpattern, expression
FROM@EXPRTABLE
JOIN @TRANSLATETABLE
ON expression NOT LIKE REPLACE(pattern, '#', '%')
I would advise not to use that in production though - the REPLACE being used in the JOIN will give terrible performance. Good enough for proof of concept though ... but I'm not sure if that is the answer to the question that you are asking? 🙂
September 19, 2015 at 4:15 pm
Kristen,
Thanks. That doesn't exactly produce the results I'm looking for but what if I split the string into rows based on a delimiter (e.g., the "#" sign). Then, each row would contain the string up to the point at which I want to get the values. I wonder if that could then be used with a combination of LEN, CHARINDEX, and SUBSTRING functions and the expression itself, to get the correct results.
For example, the pattern CALL("#","#","#") would be split and joined to the expression table.
pattern | expression
CALL(" | CALL("-","m","TEST")
"," | CALL("-","m","TEST")
"," | CALL("-","m","TEST")
") | CALL("-","m","TEST")
I would like the end result to be -mTEST
Any thoughts?
Mike Scalise, PMP
https://www.michaelscalise.com
September 19, 2015 at 11:15 pm
I won't call this an elegant solution by any stretch of the imagination, but it's the only "1:00 am" solution that's coming to mind at the moment...
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp;
SELECT
t.Pattern,
DENSE_RANK() OVER (ORDER BY e.expression) AS ExpressionNum,
sc.ItemNumber,
e.expression,
e.expression AS Result,
sc.Item
INTO #Temp
FROM
@TRANSLATETABLE t
JOIN @EXPRTABLE e
ON e.expression LIKE (REPLACE(t.pattern, '#', '%'))
CROSS APPLY dbo.SplitCSVToTable8K(t.pattern, '#') sc;
--SELECT * FROM #Temp t;
DECLARE
@en INT = 0,
@in INT = 0,
@i VARCHAR(100);
WHILE EXISTS (SELECT 1 FROM #temp t WHERE t.ExpressionNum > @en)
BEGIN
SELECT @en = MIN(t.ExpressionNum) FROM #Temp t WHERE t.ExpressionNum > @en;
WHILE EXISTS (SELECT 1 FROM #Temp t WHERE t.ExpressionNum = @en AND t.ItemNumber > @in)
BEGIN
SELECT @in = MIN(t.ItemNumber) FROM #Temp t WHERE t.ExpressionNum = @en AND t.ItemNumber > @in;
SELECT @i = t.Item FROM #Temp t WHERE t.ExpressionNum = @en AND t.ItemNumber = @in;
UPDATE #Temp SET Result = REPLACE(Result, @i, '') WHERE ExpressionNum = @en;
END;
SET @in = 0;
END;
SELECT DISTINCT
t.pattern,
t.expression,
t.Result
FROM
#Temp t;
Hopefully someone else will have a cleaner solution.
September 20, 2015 at 1:51 am
Jason A. Long (9/19/2015)
I won't call this an elegant solution by any stretch of the imagination, but it's the only "1:00 am" solution that's coming to mind at the moment...
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp;
SELECT
t.Pattern,
DENSE_RANK() OVER (ORDER BY e.expression) AS ExpressionNum,
sc.ItemNumber,
e.expression,
e.expression AS Result,
sc.Item
INTO #Temp
FROM
@TRANSLATETABLE t
JOIN @EXPRTABLE e
ON e.expression LIKE (REPLACE(t.pattern, '#', '%'))
CROSS APPLY dbo.SplitCSVToTable8K(t.pattern, '#') sc;
--SELECT * FROM #Temp t;
DECLARE
@en INT = 0,
@in INT = 0,
@i VARCHAR(100);
WHILE EXISTS (SELECT 1 FROM #temp t WHERE t.ExpressionNum > @en)
BEGIN
SELECT @en = MIN(t.ExpressionNum) FROM #Temp t WHERE t.ExpressionNum > @en;
WHILE EXISTS (SELECT 1 FROM #Temp t WHERE t.ExpressionNum = @en AND t.ItemNumber > @in)
BEGIN
SELECT @in = MIN(t.ItemNumber) FROM #Temp t WHERE t.ExpressionNum = @en AND t.ItemNumber > @in;
SELECT @i = t.Item FROM #Temp t WHERE t.ExpressionNum = @en AND t.ItemNumber = @in;
UPDATE #Temp SET Result = REPLACE(Result, @i, '') WHERE ExpressionNum = @en;
END;
SET @in = 0;
END;
SELECT DISTINCT
t.pattern,
t.expression,
t.Result
FROM
#Temp t;
Hopefully someone else will have a cleaner solution.
Quick question Jason, can you post the source for the dbo.SplitCSVToTable8K function?
😎
September 20, 2015 at 5:23 am
Eirikur Eiriksson (9/20/2015)
Quick question Jason, can you post the source for the dbo.SplitCSVToTable8K function?😎
It's simply a renamed version of Jeff Moden's DelimitedSplit8k function. It was renamed to fit an existing naming convention.
September 20, 2015 at 5:51 am
Jason A. Long (9/20/2015)
Eirikur Eiriksson (9/20/2015)
Quick question Jason, can you post the source for the dbo.SplitCSVToTable8K function?😎
It's simply a renamed version of Jeff Moden's DelimitedSplit8k function. It was renamed to fit an existing naming convention.
I though that was the case;-)
😎
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply