String Change in T-SQL

Question

Hi All,

I have a requirement, i want to replace string with id value from another table value.

Another table id's:
4698,50470,50490,4698,5047

ID NAME
4698 ABC
5047 DEF
5049 GHI

Source String : (subitemid_4698&& (subitemid_5047>=0 ||subitemid_GHI>=0)) || (!subitemid_ABC && subitemid_DEF==0 && subitemid_GHI==0)

Ouput String: (subitemid_ABC&& (subitemid_DEF>=0 ||subitemid_5049>=0)) || (!subitemid_4698 && subitemid_5047==0 && subitemid_5049==0)

/Below is the Query i tried/

------------------------------------------------------------------------------------------------------------------

DECLARE @strNumbers VARCHAR(100) = '(productid_4698&& (productid_5047>=0 ||productid_5049>=0)) || (!productid_4698 && productid_5047==0 && productid_5049==0)'
DECLARE @Inputparms VARCHAR(100)
DECLARE @POS SMALLINT = 0
SET @POS = PATINDEX('%[^_0-9]%', ISNULL(@strNumbers,0)) --Find first character

WHILE (@POS > 0)
BEGIN
SET @strNumbers = STUFF(@strNumbers, @POS , 1, '')
SET @POS = PATINDEX('%[^_0-9]%', @strNumbers)
END

SET @Inputparms= (SELECT REPLACE(RIGHT(@strNumbers, LEN(@strNumbers) - 1),'_',','))

select @Inputparms

----------------------------------------------------------------------------------------------------------

Thanks in Advance...

Answer 1

Your post is not fully coherent. In your source string there are both ID and names, and in the output they appears to have been replaced in either direction. And the SQL code you have does not really match the description.

The attached code is my guess of what you are looking for. The code is not particularly funny, I would consider a non-SQL solution, if I were you. (The reason I attach the code rather than including in the post is because there is an issue in the forum where some content is rejected by an overly zealous filter, as discussed here:

19598-slask.txt

Answer 2

Hi @BHVS ,

Please refer below and check whether it is helpful to you. Otherwise please provide more details as mentioned by other experts. Thanks.

drop table if exists test1  
  
create table test1  
(  
ID int primary key,  
NAME varchar(20)  
)  
  
insert into test1 values   
(4698,'ABC'),  
(5047,'DEF'),  
(5049,'GHI')  
  
DECLARE @strNumbers VARCHAR(100) = '(productid_4698&& (productid_5047>=0 ||productid_5049>=0)) || (!productid_4698 && productid_5047==0 && productid_5049==0)'  
  
select @strNumbers = REPLACE(@strNumbers, ID, NAME) from test1  
  
select @strNumbers  

Output:

(productid_ABC&& (productid_DEF>=0 ||productid_GHI>=0)) || (!productid_ABC && productid_DEF==0   

If the response is helpful, please click "Accept Answer" and upvote it.

Best regards
Melissa

Answer 3

Hi @BHVS ,

Please refer the attached txt file with detailed query to output below since the code could not be posted here.

Source String : (subitemid_4698&& (subitemid_5047>=0 ||subitemid_GHI>=0)) || (!subitemid_ABC && subitemid_DEF==0 && subitemid_GHI==0)

Ouput String: (subitemid_ABC&& (subitemid_DEF>=0 ||subitemid_5049>=0)) || (!subitemid_4698 && subitemid_5047==0 && subitemid_5049==0)

20143-u.txt

If the response is helpful, please click "Accept Answer" and upvote it.

Best regards
Melissa

Answer 4

Hi @BHVS ,

Source String: (subitemid_ABC&& (subitemid_DEF>=0 ||subitemid_5049>=0)) || (!subitemid_4698 && subitemid_5047==0 && subitemid_5049==0)
Output String : (subitemid_4698&& (subitemid_5047>=0 ||subitemid_GHI>=0)) || (!subitemid_ABC && subitemid_DEF==0 && subitemid_GHI==0)

Please create below function [SplitString] firstly, refer the test1 DDL from above and find the query in attached txt file.

[CREATE FUNCTION [dbo].[SplitString]  
(  
    @List NVARCHAR(MAX),  
    @Delim VARCHAR(255)  
)  
RETURNS TABLE  
AS  
    RETURN ( SELECT [Value] FROM   
      (   
        SELECT   
          [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],  
          CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))  
        FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)  
          FROM sys.all_objects) AS x  
          WHERE Number <= LEN(@List)  
          AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim  
      ) AS y  
    );  

20065-u.txt

If the response is helpful, please click "Accept Answer" and upvote it.

Best regards
Melissa