How to find specific characters in a string and replace them with values fetched from a table in SQL Server

Question

I have text stored in the table "StructureStrings"

Create Table StructureStrings(Id INT Primary Key,String nvarchar(4000))

Sample Data:

Id String
1 Select * from Employee where Id BETWEEN ### and ### and Customer Id> ### 2 Select * from Customer where Id BETWEEN ### and ### 3 Select * from Department where Id=###

and I want to replace the "###" word with a values fetched from another table named "StructureValues"

 Create Table StructureValues (Id INT Primary Key,Value nvarcrhar(255))
        Id Value 
        1  33
        2  20
        3  44

I want to replace the "###" token present in the strings like

      Select * from  Employee where Id BETWEEN 33 and 20 and Customer Id> 44
      Select * from  Customer where Id BETWEEN 33 and 20
      Select * from  Department where Id=33

PS: 1. Here an assumption is that the values will be replaced with the tokens in the same order i.e first occurence of "###" will be replaced by first value of "StructureValues.Value" column and so on.

can we safely assume nvarchar(40000) is nvarchar(4000)? nvarchar(40000) is an invalid data type. — Thom A
– Thom A ♦, Commented Dec 28, 2017 at 10:02
@Larnu Yes infact its 4000 , thanks for the info, I have edited the question — Siddharth Bharadwaj
– Siddharth Bharadwaj, Commented Dec 28, 2017 at 10:04
With the latest edit, this is now impossible; or at least if there is the performance will be horrific. Especially with the use the replacement string '###' This means that there are an indeterminable amount of parameters, and they could be any data type. You need to seriously reconsider your set up. There also seem to now be no relationship between Ids. Unless Select * from Employee where Name like '%###%' will be replaced to Select * from Employee where Name like '%44%' (what employee would have a name with digits in..?)? If so, what is the string value for Id 4? — Thom A
– Thom A ♦, Commented Dec 28, 2017 at 10:26
@Larnu apologies for bad db design, I think I will keep the number and the text values in separate tables — Siddharth Bharadwaj
– Siddharth Bharadwaj, Commented Dec 28, 2017 at 10:36
There's still not any kind of obvious relationship though. Why does Id 1 in StructureStrings relate to Ids 1 and 2 in NumberStructureValues? Why does Id 3 in StructureStrings relate to Ids 4 and then 2 (in THAT order...?) in the table TextStructureValues. How does 3 become 4 and then 2..? There is NO relationship here. Your data needs a to be able to relate to get the answer you want. if you can't relate the data, the task is impossible. — Thom A
– Thom A ♦, Commented Dec 28, 2017 at 10:41

Thom A · Accepted Answer · 2017-12-28 12:00:05Z

1

Posting this as a new answer, rather than editting my previous.

This uses Jeff Moden's DelimitedSplit8K; it does not use the built in splitter available in SQL Server 2016 onwards, as it does not provide an item number (thus no join criteria).

You'll need to firstly put the function on your server, then you'll be able to use this. DO NOT expect it to perform well. There's a lot of REPLACE in this, which will hinder performance.

SELECT (SELECT REPLACE(DS.Item, '###', CONVERT(nvarchar(100), SV.[Value]))
        FROM StructureStrings sq
             CROSS APPLY DelimitedSplit8K (REPLACE(sq.String,'###','###|'), '|') DS --NOTE this uses a varchar, not an nvarchar, you may need to change this if you really have Unicode characters
             JOIN StructureValues SV ON DS.ItemNumber = SV.Id
        WHERE SS.Id = sq.id
        FOR XML PATH ('')) AS NewString
FROM StructureStrings SS;

If you have any question, please place the comments on this answer; do not put them under the question which has already become quite a long discussion.

answered Dec 28, 2017 at 12:00

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Siddharth Bharadwaj Over a year ago

This did exactly what I wanted Thanks Larnu.

Siddharth Bharadwaj Over a year ago

I have implemented this solution but somehow the "AND" operator is getting omitted ex :SELECT * FROM UAA_AUDIT_DETAILS WHERE CATEGORY_ID = 1 AND TIME_STAMP BETWEEN #23:00#04:00

Danieboy · Accepted Answer · 2017-12-28 10:18:59Z

0

Maybe this is what you are looking for.

DECLARE @Employee TABLE (Id int)
DECLARE @StructureValues  TABLE (Id int, Value int)

INSERT INTO @Employee
VALUES (1), (2), (3), (10), (15), (20), (21)

INSERT INTO @StructureValues
VALUES (1, 10), (2, 20)

SELECT * 
FROM @Employee 
WHERE Id BETWEEN (SELECT MIN(Value) FROM @StructureValues) AND (SELECT MAX(Value) FROM @StructureValues)

answered Dec 28, 2017 at 10:18

Danieboy

4,5216 gold badges39 silver badges60 bronze badges

1 Comment

Danieboy Over a year ago

@SiddharthBharadwaj If this is not correct, tell me your exact input and the exact output you want. Because it's currently unclear exactly what you are looking to do.

Thom A · Accepted Answer · 2017-12-28 11:03:18Z

Very different take here:

CREATE TABLE StructureStrings(Id int PRIMARY KEY,String nvarchar(4000));

INSERT INTO StructureStrings
VALUES (1,'SELECT * FROM Employee WHERE Id BETWEEN ### AND ###'),
       (2,'SELECT * FROM Customer WHERE Id BETWEEN ### AND ###');

CREATE TABLE StructureValues (Id int, [Value] int);
INSERT INTO StructureValues
VALUES (1,10),
       (2,20);
GO

DECLARE @SQL nvarchar(4000);
--I'm asuming that as you gave one output you are supplying an ID or something?
DECLARE @Id int = 1;

WITH CTE AS(
    SELECT SS.Id,
           SS.String,
           SV.[Value],
           LEAD([Value]) OVER (ORDER BY SV.Id) AS NextValue,
           STUFF(SS.String,PATINDEX('%###%',SS.String),3,CONVERT(varchar(10),[Value])) AS ReplacedString
    FROM StructureStrings SS
         JOIN StructureValues SV ON SS.Id = SV.Id)
SELECT @SQL = STUFF(ReplacedString,PATINDEX('%###%',ReplacedString),3,CONVERT(varchar(10),NextValue))
FROM CTE
WHERE Id = @Id;

PRINT @SQL;
--EXEC (@SQL); --yes, I should really be using sp_executesql

GO
DROP TABLE StructureValues;
DROP TABLE StructureStrings;

Edit: Note that Id 2 will return NULL, as there isn't a value to LEAD to. If this needs to change, we'll need more logic on what the value should be if there is not value to LEAD to.

Edit 2: This was based on the OP's original post, not what he puts it as later. As it currently stands, it's impossible.

Collectives™ on Stack Overflow

How to find specific characters in a string and replace them with values fetched from a table in SQL Server

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related