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

Subscribe to my newsletter and never miss my upcoming articles

Play this article

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.

Impressum