Replace parts of a string in sql-CodePudding

I have a column which contains the string in the below format:

Account Number [1.######] belongs to customer [2.########] residing in [3.######] for the selected period.

I want to replace the placeholders of 1, 2 and 3 with actual value which will come from a table.

Something like below:

Account Number 1234 belongs to John residing in London for the selected period.

Any help would be greatly appreciated.

Thanks in advance.

Regards, Ram

CodePudding user response：

Starting from SQL Server 2016 onwards, it supports an enhanced version of the FORMATMESSAGE() function.

Message can have a maximum of 2,047 characters.

Here is a conceptual example of it.

SQL

DECLARE @message VARCHAR(2048) = 'Account Number %s belongs to customer %s residing in %s for the selected period.';
DECLARE @AccountNo VARCHAR(20) = '1234'
    , @Customer VARCHAR(20) = 'John'
    , @Location VARCHAR(20) = 'London'
    , @Result VARCHAR(2048);

SET @Result = FORMATMESSAGE(@message
            , @AccountNo
            , @Customer
            , @Location);

-- test
SELECT @Result;

Output

Account Number 1234 belongs to customer John residing in London for the selected period.

CodePudding user response：

In your select statement add the following:

[New Field] = CONCAT('Account ',table.field,' belongs to customer ',table.field,'residing in 'table.field' for theselected period.')