Help with string manipulation in SQL

  • 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

  • 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

  • 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.

  • 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