tsql - SQL Server REPLACE()

Question

If I have a dynamic query in SQL Server which contains a variable of column names first_name, last_name, ref_ID, order_ID, post_code_ID how can I replace the all of the values suffixed _ID with the same string for example look_up_value.

Desired output would be

first_name, last_name, look_up_value, look_up_value, look_up_value

Currently have SELECT REPLACE(@Var, '_ID', 'look_up_value') but that obviously leaves the prefix of the column.

Thanks

DECLARE @SQL NVARCHAR(MAX)
DECLARE @Fields NVARCHAR(MAX)      
DECLARE @Fields2 NVARCHAR(MAX)                         
DECLARE @Return NVARCHAR(MAX) = Char(13)                                        
                              
SET @Fields = ''

SELECT @Fields = @Fields + Column_name + ',' + @Return
FROM Orders.INFORMATION_SCHEMA.columns 
WHERE TABLE_NAME = 'Recent_Orders'

SELECT @Fields = LEFT(@Fields, (LEN(@Fields) - 2))

SELECT REPLACE(@Fields, '_ID', 'look_up_value')

question from:https://stackoverflow.com/questions/65873229/sql-server-replace

Answer 1

....    
SELECT @Fields = @Fields + case when Column_name like '%[_]ID' then 'look_up_value' else Column_name end + ',' + @Return
....