Help with string manipulation in SQL

• alex.palmer

  SSCrazy

  Points: 2292

  More actions

  March 15, 2018 at 9:08 am

  #327813

  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

  • The variables always appear between <  >
  • There can be multiple variables in a single string
  • the variable names can contain spaces
  • they always have a .number , the number is the PK for the Variable table.

  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

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  March 15, 2018 at 9:48 am

  #1983425

  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

• alex.palmer

  SSCrazy

  Points: 2292

  More actions

  March 15, 2018 at 9:52 am

  #1983426

  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.

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  March 19, 2018 at 1:18 pm

  #1983822

  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)