How to Replace %S with Feature Value on Feature String Based on Feature Name and partid?

Question

How to Replace %S with Feature Value on Feature String Based on Feature Name and partid?

ahmed salah 3,216

I work on sql server 2017 i need write select query Replace %S with

Feature Value on Column Feature String Based On partid and Feature Name Related .

as example

partid 1211 have Feature String AC(%S)Boil(%S)Temp(%S)
I need to replace %S with Feature Value related to Feature Name For same Part so AC(%S) will be AC(5v)

and Boil(%S) will be Boil(10v)

and Temp(%S) will be Temp(5v)

so New Column Feature Value String will be AC(5V)Boil(10v)Temp(5V)

i have feature string like that AC(%S)Boil()Temp(%S)

so it will be as below

AC(5V)Boil()Temp(5V)

if i have feature string like that AC(%S)Boil(%S)

THEN it will be as

AC(5V)Boil(10v)

my 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)')

final result

Locked Question. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

Jingyang Li 5,901 Reputation points Volunteer Moderator

2022-06-09T18:37:38.273+00:00

Why are you asking question without trying the solution provided to you in your earlier question?
The solution(s) should work for you.
ahmed salah 3,216 Reputation points

2022-06-09T21:23:10.817+00:00

solution here different
here i must replace %S With feature value
because i have features not have %S
ahmed salah 3,216 Reputation points

2022-06-09T21:52:12.897+00:00

to more explain
i have feature string like that AC(%S)Boil()Temp(%S)
so it will be as below
AC(5V)Boil()Temp(5V)

if i have feature string like that AC(%S)Boil(%S)
THEN it will be as
AC(5V)Boil(10v)

so the solution provided to you in your earlier question will not solve my issue
Jingyang Li 5,901 Reputation points Volunteer Moderator

2022-06-10T03:39:02.783+00:00

I don't see the case (difference) you described in your sample data and expected result.
Maybe you know it but we cannot guess.
Bert Zhou-msft 3,521 Reputation points

2022-06-10T06:06:18.157+00:00

]1
As to how the two compare , I'm not quite sure what you're describing . Are you trying to compare lengths between featurevaluestrings? For example , Partld is 6211. Compared with the string of 2421, do you have anything to add?

Bert Zhou
ahmed salah 3,216 Reputation points

2022-06-10T07:26:40.957+00:00

i Edit my original post

Answer accepted by question author

LiHong-MSFT 10,061

Hi @ahmed salah
Check this query:

;WITH CTE1 AS  
(  
SELECT *,CASE WHEN CHARINDEX(FeatureName,FeatureString)>0 AND SUBSTRING(FeatureString,CHARINDEX(FeatureName,FeatureString)+LEN(FeatureName)+1,2)='%S'  
              THEN CONCAT(FeatureName,'(',FeatureValue,')')   
              WHEN CHARINDEX(FeatureName,FeatureString)>0 AND SUBSTRING(FeatureString,CHARINDEX(FeatureName,FeatureString)+LEN(FeatureName)+1,2)<>'%S'  
              THEN CONCAT(FeatureName,'()')  
              ELSE '' END AS FeatureValueString  
FROM #partsfeature  
)  
SELECT *,(SELECT STRING_AGG(FeatureValueString,'')WITHIN GROUP(ORDER BY FeatureValueString)   
                FROM CTE1 WHERE PartId = p.PartId) AS FeatureValueString  
FROM #partsfeature p

If your SQL Sever don't support STRING_AGG, then try this:

;WITH CTE1 AS  
(  
SELECT *  
      ,CASE WHEN CHARINDEX(FeatureName,FeatureString)>0 AND SUBSTRING(FeatureString,CHARINDEX(FeatureName,FeatureString)+LEN(FeatureName)+1,2)='%S'  
                THEN CONCAT(FeatureName,'(',FeatureValue,')')   
                WHEN CHARINDEX(FeatureName,FeatureString)>0 AND SUBSTRING(FeatureString,CHARINDEX(FeatureName,FeatureString)+LEN(FeatureName)+1,2)<>'%S'  
                THEN CONCAT(FeatureName,'()')  
                ELSE '' END AS FeatureValueString  
FROM #partsfeature  
),CTE2 AS  
(  
SELECT B.PartId,STUFF((SELECT ''+FeatureValueString FROM CTE1 A WHERE A.PartId=B.PartId Order by featureName  
                                     FOR XML PATH('')),1,0,'') AS FeatureValueString   
FROM CTE1 B   
GROUP BY B.PartId  
)  
SELECT C.PartId,P.FeatureName,P.FeatureValue,FeatureString,FeatureValueString  
FROM CTE2 C INNER JOIN #partsfeature P on P.PartId=C.PartId

Best regards,
LiHong

ahmed salah 3,216 Reputation points

2022-06-10T08:38:39.897+00:00

thanks you very much solved

1 additional answer

Jingyang Li 5,901 Reputation points Volunteer Moderator

2022-06-09T18:37:38.273+00:00

Why are you asking question without trying the solution provided to you in your earlier question?
The solution(s) should work for you.
ahmed salah 3,216 Reputation points

2022-06-09T21:23:10.817+00:00

solution here different
here i must replace %S With feature value
because i have features not have %S
ahmed salah 3,216 Reputation points

2022-06-09T21:52:12.897+00:00

to more explain
i have feature string like that AC(%S)Boil()Temp(%S)
so it will be as below
AC(5V)Boil()Temp(5V)

if i have feature string like that AC(%S)Boil(%S)
THEN it will be as
AC(5V)Boil(10v)

so the solution provided to you in your earlier question will not solve my issue
Jingyang Li 5,901 Reputation points Volunteer Moderator

2022-06-10T03:39:02.783+00:00

I don't see the case (difference) you described in your sample data and expected result.
Maybe you know it but we cannot guess.
Bert Zhou-msft 3,521 Reputation points

2022-06-10T06:06:18.157+00:00

]1
As to how the two compare , I'm not quite sure what you're describing . Are you trying to compare lengths between featurevaluestrings? For example , Partld is 6211. Compared with the string of 2421, do you have anything to add?

Bert Zhou
ahmed salah 3,216 Reputation points

2022-06-10T07:26:40.957+00:00

i Edit my original post
ahmed salah 3,216 Reputation points

2022-06-10T08:38:39.897+00:00

thanks you very much solved

Answer 1

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

1 deleted comment

Comments have been turned off. Learn more