SQL Is it possible to incorporate a SELECT with a REPLACE?

December 22, 2022

I’m using MS SQL Server 2019

I have a string in a table (tblJobCosts) that has its own ID like this:

TextID   jobText
   1     Total Cost for job is £[].  This includes VAT

How do I update the value stored in the brackets based on the value from another table?

The end result would look like this:

Total Cost for job is £500.  This includes VAT

I thought I could incorporate a SELECT with a REPLACE but this does not seem possible:

DECLARE @JobNum INT = 123;
  
  UPDATE dbo.JobCosts
  SET jobText = REPLACE (jobText,'[]', 
  SELECT JH.jobCost
  FROM dbo.JobHead AS JH 
   WHERE (JH.JobNo = @JobNum)
   ) AND TextID = 1

If I run the above I receive the error:

Incorrect syntax near the keyword 'SELECT'.

Is it possible to incorporate a SELECT with a REPLACE?

>Solution :

I think that you cannot call a select statement in the replace function.
I would try something like that:

 UPDATE dbo.JobCosts
  SET jobText = REPLACE (jobText,'[]',k.the_cost) from
 
 ( SELECT JH.jobCost as the_cost
   FROM dbo.JobHead AS JH 
   WHERE (JH.JobNo = @JobNum)
   )k 
where TextID = 1