1

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 "value" 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)

5
  • 1
    Welcome to the site! Your question is difficult to answer because it's unclear what platform you are using.
    – Andriy M
    Commented Jan 13, 2021 at 8:58
  • 3
    As an aside, though, if you are using a relational database product, please consider either parsing the PARAMETERLIST value and storing each parameter in a separate column (so that you can manipulate their values much more easily), or do this kind of processing in the application. Many SQL products have a set of string functions, and that can be very handy, but what you are trying to do here (change the value of a field/column) is something that an RDBMS is designed to do natively if you store your data in a normalised fashion. Just saying.
    – Andriy M
    Commented Jan 13, 2021 at 8:58
  • Hello Andriy, thank you for your answer. We're using Microsoft SQL Server Enterprise (Ver. 14.0.1000.169). I've edited the initiating post.
    – S_Koenig
    Commented Jan 13, 2021 at 9:46
  • Andriy, please provide me a code example :-)
    – S_Koenig
    Commented Jan 13, 2021 at 9:49
  • Build 14.0.1000.169 is SQL Server 2017 not SQL Server 2012.
    – Brian
    Commented Jan 13, 2021 at 15:35

2 Answers 2

3

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);
1

I want to change a part of some strings in the PARAMETERLIST column.

This is just one of the reasons that you do not store multiple data items in a single column.

Any "string" manipulation on these values is going to be complicated, cumbersome and, most importantly, slow. Don't do it (any more).

Normalise your data and break these mini-monoliths out into something more sensible, perhaps like this:

+-------------+------------+-------+---------------+--------------+
| APGRPID     | VIRTKEY    | LFDNR | PARAM_NAME    | PARAM_VALUE  |
+-------------+------------+-------+---------------+--------------+ 
| 1091.000000 | 121.000000 |     1 | KF            | 138          | 
| 1091.000000 | 121.000000 |     1 | SCANANDSEND   | TRUE         | 
| 1091.000000 | 121.000000 |     1 | ENDBARCODE    | 999999999999 | 
| 1091.000000 | 121.000000 |     1 | PATH          | K:\SSTEST    | 
| 1091.000000 | 121.000000 |     1 | BARCODELENGTH | 8            | 
| 2092.000000 | 130.000000 |     8 | KF            | 138          | 
| 2092.000000 | 130.000000 |     8 | PATH          | s:\          | 
+-------------+------------+-------+---------------+--------------+

Sure, it doesn't look as "natural" to you and me but, as far as your database is concerned, this is the way to go.

Alternatively, you might consider a column for each parameter - it depends on how often the "list" of possible parameters changes and, whilst it means you can have the correct Data Type for each parameter, it may actually wind up being more fiddly to work with, having to second-guess whether each retrieved value is NULL or not. YMMV.

+-------------+------------+-------+---------------+--------------+-----+-----------+-------------+
| APGRPID     | VIRTKEY    | LFDNR | BARCODELENGTH | ENDBARCODE   | KF  | PATH      | SCANANDSEND | 
+-------------+------------+-------+---------------+--------------+-----+-----------+-------------+
| 1091.000000 | 121.000000 |     1 |           138 | 999999999999 | 138 | K:\SSTEST | TRUE        | 
| 2091.000000 | 130.000000 |     8 | NULL          | NULL         | 138 | S:\       | NULL        | 
+-------------+------------+-------+---------------+--------------+-----+-----------+-------------+

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.