March 15, 2018 at 9:08 am
Hi
First off can I say I'm more of an infrastructure DBA than a developer DBA so sorry if I'm asking a stupid question.......
I have a database with two tables one has a text column that includes variables names in the text and the other table contains the values for the variables.
So in table 1 the text column may say something like
Do this to <variable1.544> and then do this to <Another Variable.447> exit
I'd like to be able to parse this text string and replace all the variable names with their looked up values from the variable table.
Can someone offer any advice on how to parse this string and identify the variables and PK's?
Thanks
Alex
March 15, 2018 at 9:48 am
Alex
T-SQL probably isn't the best tool for this job, but that doesn't mean it can't be done.
DECLARE @String varchar(max) = 'Do this to <variable1.544> and then do this to <Another Variable.447> exit';
WITH Ten(n) AS (
SELECT n
FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n)
)
, Hundred AS (
SELECT t1.n
FROM Ten t1
CROSS JOIN Ten t2
)
, Thousand(n) AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM Hundred
CROSS JOIN Ten
)
, Characters(n, Incidence, TheChar) AS (
SELECT
n
, ROW_NUMBER() OVER (PARTITION BY SUBSTRING(@String,n,1) ORDER BY n)
, SUBSTRING(@String,n,1)
FROM Thousand
)
, StartsandEnds AS (
SELECT
Incidence
, MIN(n) AS Startvar
, MAX(n) AS Endvar
FROM Characters
WHERE TheChar IN ('<','>')
GROUP BY Incidence
)
, Variables(Incidence, TheVar) AS (
SELECT
Incidence
, SUBSTRING(@String,Startvar+1,Endvar-Startvar-1)
FROM StartsandEnds
)
SELECT
Incidence
, LEFT(TheVar,CHARINDEX('.',TheVar)-1) AS VarName
, RIGHT(TheVar,LEN(TheVar)-CHARINDEX('.',TheVar)) AS VarNumber
FROM Variables;
John
March 15, 2018 at 9:52 am
Wow......
Thanks for that John. It's at the end of my working day now but I'll try and understand how that all hangs together tomorrow.
March 19, 2018 at 1:18 pm
If there are consistencies in the method of that variable appearing, which at the moment appear to be:
1.) Starting delimiter = <
2.) Ending delimiter = >
3.) Splitting delimiter = period (.)
Try this:DECLARE @String varchar(1000) = 'Do this to <variable1.544> and then do this to <Another Variable.447> exit';
WITH Strings AS (
SELECT
CHARINDEX('<', @String, 1) + 1 AS StartPos,
CHARINDEX('>', @String, CHARINDEX('<', @String, 1) + 1) AS EndPos
UNION ALL
SELECT
CHARINDEX('<', @String, S.EndPos) + 1,
CHARINDEX('>', @String, S.EndPos + 1)
FROM Strings AS S
WHERE CHARINDEX('<', @String, S.EndPos) > 0
)
SELECT *,
SUBSTRING(V.Sub_String, 1, CHARINDEX('.', V.Sub_String) - 1) AS VarName,
SUBSTRING(V.Sub_String, CHARINDEX('.', V.Sub_String) + 1, LEN(V.Sub_String)) AS VarValue
FROM Strings AS S1
CROSS APPLY (VALUES (SUBSTRING(@String, S1.StartPos, S1.EndPos - S1.StartPos))) AS V (Sub_String);
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply