Replace %S with Feature Value on Feature String Based on Feature Name and Partid

All we need is an easy explanation of the problem, so here it is.

I need to write a select statement that will replace a %S string in the FeatureString column with the actual values stored in the same table for a given PartID.

With the following example data:

+--------+--------------+---------------+------------------------+
| PartID |  FeatureName |  FeatureValue |      FeatureString     |
+--------+--------------+---------------+------------------------+
|   1211 | AC           | 5V            | AC(%S)Boil(%S)Temp(%S) |
|   1211 | Boil         | 10v           | AC(%S)Boil(%S)Temp(%S) |
|   1211 | Temp         | 5V            | AC(%S)Boil(%S)Temp(%S) |
+--------+--------------+---------------+------------------------+

I would like to retrieve the following FeatureValueString:

+--------+-------------------------+
| PartID |       FeatureName       |
+--------+-------------------------+
|   1211 | AC(5V)Boil(10v)Temp(5V) |
+--------+-------------------------+

Explanation

I need to replace the %S part of the FeatureString with the values that are stored in the corresponding FeatureNameFeatureValue column combinations.

The last PartID with the value 7791 is a bit of a special case, as it only requires two of the values stored in the table. These being AC and Boil. The value for Temp isn’t required in the FeatureString returned.

Sample Data

  create table #partsfeature
 (
 PartId int,
 FeatureName varchar(300),
 FeatureValue varchar(300),
 FeatureString varchar(300)
 )
  insert into #partsfeature(PartId,FeatureName,FeatureValue,FeatureString)
  values
  (1211,'AC','5V','AC(%S)Boil(%S)Temp(%S)'),
  (1211,'Boil','10v','AC(%S)Boil(%S)Temp(%S)'),
  (1211,'Temp','5V','AC(%S)Boil(%S)Temp(%S)'),
  (2421,'grail','51V','Alc(%S)Coil(%S)grail(%S)'),
  (2421,'Coil','9V','Alc(%S)Coil(%S)grail(%S)'),
  (2421,'Alc','5V','Alc(%S)Coil(%S)grail(%S)'),
  (6211,'compress','33v','compress(%S)heat(%S)push(%S)'),
  (6211,'heat','90v','compress(%S)heat(%S)push(%S)'),
  (6211,'push','80v','compress(%S)heat(%S)push(%S)'),

  (5442,'compress','33v','compress(%S)heat()push(%S)'),
  (5442,'heat','90v','compress(%S)heat()push(%S)'),
  (5442,'push','80v','compress(%S)heat()push(%S)'),

  (7791,'AC','5V','AC(%S)Boil(%S)'),
  (7791,'Boil','10v','AC(%S)Boil(%S)'),
  (7791,'Temp','5V','AC(%S)Boil(%S)'),


  (8321,'Angit','50V','Angit(%S)Fan(%S)Hot(%S),Wether(%S)'),
  (8321,'Fan','9v','Angit(%S)Fan(%S)Hot(%S),Wether(%S)'),
  (8321,'Hot','3V','Angit(%S)Fan(%S)Hot(%S),Wether(%S)'),
  (8321,'Wether','12V','Angit(%S)Fan(%S)Hot(%S),Wether(%S)')

Screenshot of Expected Results

Replace %S with Feature Value on Feature String Based on Feature Name and Partid

There can be more than three features.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

One solution:

SELECT 
    P.*, 
    FVS.FeatureValueString 
FROM #partsfeature AS P
CROSS APPLY
(
    SELECT 
        FeatureValueString =
            STRING_AGG(
                CONCAT(
                    P2.FeatureName, 
                    '(',
                    IIF(
                        CHARINDEX(
                            P2.FeatureName + '(%S)', 
                            P2.FeatureString) > 0,
                        P2.FeatureValue,
                        ''),
                    ')'), 
                '')
                WITHIN GROUP (
                    ORDER BY CHARINDEX(P2.FeatureName, P2.FeatureString))
    FROM #partsfeature AS P2
    WHERE
        P2.PartId = P.PartId
        AND CHARINDEX(P2.FeatureName, P2.FeatureString) > 0
) AS FVS;

Another using recursive replacement:

WITH R AS
(
    SELECT DISTINCT 
        P.PartId, 
        P.FeatureString
    FROM #partsfeature AS P

    UNION ALL

    SELECT
        P.PartId,
        CONVERT(varchar(300),
            REPLACE(
                R.FeatureString, 
                P.FeatureName + '(%S)', 
                P.FeatureName + '(' + P.FeatureValue + ')'))
    FROM R
    JOIN #partsfeature AS P
        ON P.PartId = R.PartId
        AND R.FeatureString LIKE '%' + P.FeatureName + '([%]S)%'
        AND R.FeatureString NOT LIKE '%([%]S)%' + P.FeatureName + '%'
)
SELECT
    P.*,
    FeatureValueString = R.FeatureString
FROM R
JOIN #partsfeature AS P
    ON P.PartId = R.PartId
WHERE 
    R.FeatureString NOT LIKE '%([%]S)%';

db<>fiddle online demo

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply