Replace specific part of a string I've got a table with multiple columns, APGRPID, VIRTKEY, LFDNR and PARAMETERLIST. I want to change a part of some strings in the PARAMETERLIST column.
In the PARAMETERLIST I want to replace the &quot;value&quot; for the PATH= setting.
Table:




APGRPID
VIRTKEY
LFDNR
PARAMETERLIST




1091.000000
121.000000
1
KF=138;SCANANDSEND=TRUE;ENDBARCODE=999999999999;PATH=K:\SSTEST;BARCODELENGTH=8


2092.000000
130.000000
8
KF=138;  PATH=s:\


2101.000000
114.000000
3
DESIGNATION=JOB;  KF=81;  PATH=w:;  SENDALLDATA=TRUE


2102.000000
116.000000
3
KF=76;  PATH=\srv05\L3OERAPMFC;  SENDALLDATA=TRUE


2303.000000
114.000000
2
KF=386;  PATH=W:;  SENDALLDATA=TRUE




The main problem I have is the randomness of the position of the PATH= parameter in the PARAMETERLIST column and the variability of the number of elements in the column if I try to CROSS APPLY a STRING_SPLIT() or anything.
Platform: Microsoft SQL Server Enterprise (Ver. 14.0.1000.169)
Given your test data, this will parse out the section of the string you want between either the first semi-colon after the PATH=, or the end of the string if that doesn't exist. From here, you should be able to use an update to replace that section of the string.
Further reading: Get The Text Between Two Delimiters
SELECT 
    x.*,
    SUBSTRING(x.PARAMETERLIST,
        CHARINDEX('PATH=', x.PARAMETERLIST) + LEN('PATH='),
        CASE WHEN x.PARAMETERLIST NOT LIKE '%PATH=%;%'
             THEN LEN(x.PARAMETERLIST)
             ELSE CHARINDEX(';', x.PARAMETERLIST, CHARINDEX('PATH=', x.PARAMETERLIST) + LEN('PATH='))
                  - LEN('PATH=') - CHARINDEX('PATH=', x.PARAMETERLIST)
        END
             ) AS parsed_string
FROM
(
    VALUES
        ('KF=138;SCANANDSEND=TRUE;ENDBARCODE=999999999999;PATH=K:\SSTEST;BARCODELENGTH=8'),
        ('KF=138; PATH=s:\'),
        ('DESIGNATION=JOB; KF=81; PATH=w:; SENDALLDATA=TRUE'),
        ('KF=76; PATH=\srv05\L3OERAPMFC; SENDALLDATA=TRUE'),
        ('KF=386; PATH=W:; SENDALLDATA=TRUE')
) AS x(PARAMETERLIST);