Rajanand Ilangovan
Rajanand Ilangovan

Rajanand Ilangovan

How to replace the first instance of the string in T-SQL?

Rajanand Ilangovan's photo
Rajanand Ilangovan
·Jul 6, 2022·

1 min read

You can use the combination of stuff, charindex and len function to replace the first occurrence of the string.


declare @database_name nvarchar(max);
declare @command_text nvarchar(max);
declare @update_command_text nvarchar(max);
set @database_name = 'MY_DATABASE';


set @command_text = '
RESTORE DATABASE [MY_DATABASE] FROM  DISK = N''\\XYZABCMNO\MSSQL_Bak\MY_DATABASE.BAK''
    WITH  FILE = 1,  MOVE N''MY_DATABASE'' TO N''M:\MSSQL_Data\MY_DATABASE.mdf''
                 ,  MOVE N''MY_DATABASE_Log'' TO N''N:\MSSQL_Log\MY_DATABASE.ldf''
                 ,  NOUNLOAD,  REPLACE,  STATS = 10
GO
'

set @update_command_text = (select stuff(@command_text, charindex(@database_name, @command_text), len(@database_name), 'TEST_'+@database_name)) 

select @command_text, @update_command_text

image.png

Thanks.