July 4, 2012 at 4:51 am
Hi All,
Below is my scenario,
I have a text say: Newdb.[DBName].dbo.TableName
Now i want to replace the word 'DBName' to 'ReplacedDBName'.
But, if the text is say: Newdb.DBName.dbo.TableName (I mean without square bracket ([) ) then i could able to achieve it.
Below is what the query i tried..
DECLARE @OrigString VARCHAR(100)
DECLARE @SearchFor VARCHAR(100)
DECLARE @ReplaceWith VARCHAR(100)
SET @SearchFor ='DBName'
SET @ReplaceWith = 'ReplacedDBName'
SET @OrigString = 'Newdb.DBName.dbo.TableName'
WHILE (PATINDEX('%.' + @SearchFor +'[^a-z]%', @OrigString) != 0)
BEGIN
SELECT @OrigString = STUFF(@OrigString, PATINDEX('%[^a-z]' + @SearchFor +'[^a-z]%', @OrigString)+1, LEN(@SearchFor), @ReplaceWith)
END
SELECT @OrigString
RESULT: Newdb.ReplacedDBName.dbo.TableName
Guide me on this.
Thanks,
Karthik.
Regards,
Karthik.
SQL Developer.
July 4, 2012 at 4:57 am
SELECT REPLACE('Newdb.[DBName].dbo.TableName','DBName','ReplacedDBName')
Or it's something else you are after?
July 4, 2012 at 5:00 am
Something like this?
DECLARE @OrigString VARCHAR(100)
DECLARE @SearchFor VARCHAR(100)
DECLARE @ReplaceWith VARCHAR(100)
SET @SearchFor ='DBName'
SET @ReplaceWith = 'ReplacedDBName'
SET @OrigString = 'Newdb.[DBName].dbo.TableName'
SET @OrigString = REPLACE(@OrigString, @SearchFor, @ReplaceWith)
SELECT @OrigString
July 4, 2012 at 5:06 am
Hi Eugene & Anthony,
I tried that REPLACE, but that will work only for some extent.
If i want to replace DBName with Replaced_DBName means we cant use REPLACE right?
So can you please give solutions that will handle this scenario too.
Thanks,
Karthik.
Regards,
Karthik.
SQL Developer.
July 4, 2012 at 5:09 am
REPLACE will work with underscores
DECLARE @OrigString VARCHAR(100)
DECLARE @SearchFor VARCHAR(100)
DECLARE @ReplaceWith VARCHAR(100)
SET @SearchFor ='DBName'
SET @ReplaceWith = 'Replaced_DBName'
SET @OrigString = 'Newdb.[DBName].dbo.TableName'
SET @OrigString = REPLACE(@OrigString, @SearchFor, @ReplaceWith)
SELECT @OrigString
July 4, 2012 at 5:17 am
Yes Anthony correct.
But, If i start replacing it for first time then it will work perfectly.
On First Execution: 'Newdb.[Replaced_DBName].dbo.TableName'
But from the next time, when it find a word DBName, then it will replace and will give the result as,
Next Executions: 'Newdb.[Replaced_Replaced_Replaced_DBName].dbo.TableName'
Did you catch my point?
Thanks,
Karthik.
Regards,
Karthik.
SQL Developer.
July 4, 2012 at 5:20 am
Only if you set the @SearchFor to a different value.
Can you post your full query and your expected outcomes.
July 4, 2012 at 5:30 am
anthony.green (7/4/2012)
REPLACE will work with underscores
DECLARE @OrigString VARCHAR(100)
DECLARE @SearchFor VARCHAR(100)
DECLARE @ReplaceWith VARCHAR(100)
SET @SearchFor ='DBName'
SET @ReplaceWith = 'Replaced_DBName'
SET @OrigString = 'Newdb.[DBName].dbo.TableName'
SET @OrigString = REPLACE(@OrigString, @SearchFor, @ReplaceWith)
SELECT @OrigString
What about this
DECLARE @OrigString VARCHAR(100)
DECLARE @SearchFor VARCHAR(100)
DECLARE @ReplaceWith VARCHAR(100)
SET @SearchFor ='.DBName.'
SET @ReplaceWith = '.ReplacedDBName.'
SET @OrigString = 'Newdb.DBName.dbo.TableName'
SET @OrigString = REPLACE(@OrigString, @SearchFor, @ReplaceWith)
SELECT @OrigString
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
July 4, 2012 at 5:41 am
Hi Anthony,
Here is an example
Daily backup will be there from Production TO TestEnvironment.
Production will have db name as 'Sample' But in UAT, we will have db name as 'Client_Sample'.
In backup process, all the SP's from Production will be restored into TestEnv.
So, When i create an SP to run using this replace function, it will look for all sp's to replace the prod dbname to testenv dbname. So, for the first time it will work perfectly.
Again, if i try executing the same sp it will work like,
Newdb.Replaced_Replaced_Replaced_DBName].dbo.TableName
I Hope explained the problem.
Regards,
Karthik.
SQL Developer.
July 4, 2012 at 5:53 am
I get what your trying to do, I just dont know how you are ending up with the replaced_replaced_replaced_dbname.
Something must be changing the search for and replace with parameters, why?
July 4, 2012 at 6:03 am
What about this one:
CREATE FUNCTION dbo.f_DBRename ( @OrigString VARCHAR(100)
,@SearchFor VARCHAR(100)
,@ReplaceWith VARCHAR(100)
)
RETURNS VARCHAR(100) WITH SCHEMABINDING
AS
BEGIN
IF @OrigString LIKE '%.' + @ReplaceWith + '.%' RETURN @OrigString
SET @SearchFor = REPLACE(REPLACE(@SearchFor,'[',''),']','')
SET @OrigString = REPLACE(@OrigString, '.[' + @SearchFor + '].','.' + @SearchFor + '.')
RETURN REPLACE(@OrigString, @SearchFor, @ReplaceWith)
END
GO
SELECT dbo.f_DBRename ('Newdb.[DBName].dbo.TableName','DBName','ReplacedDBName')
SELECT dbo.f_DBRename (dbo.f_DBRename ('Newdb.[DBName].dbo.TableName','DBName','ReplacedDBName'),'DBName','ReplacedDBName')
Please Note: It's not suitable for cases where database name contain space, but I'm sure you don't name your databases with in such way.
July 4, 2012 at 6:11 am
Another version will work for any DBName and any combination of object name parts used:
CREATE FUNCTION dbo.f_DBRenameX ( @OrigString VARCHAR(100)
,@SearchFor VARCHAR(100)
,@ReplaceWith VARCHAR(100)
)
RETURNS VARCHAR(100) WITH SCHEMABINDING
AS
BEGIN
SET @SearchFor = REPLACE(REPLACE(@SearchFor,'[',''),']','')
RETURN ISNULL(PARSENAME(@OrigString,4) + '.','')
+ ISNULL(CASE WHEN PARSENAME(@OrigString,3) = @SearchFor
THEN '[' + @ReplaceWith + ']'
ELSE '[' + PARSENAME(@OrigString,3) + ']'
END + '.','')
+ ISNULL(PARSENAME(@OrigString,2) + '.','')
+ ISNULL(PARSENAME(@OrigString,1),'')
END
GO
SELECT dbo.f_DBRenameX ('Newdb.[DBName].dbo.TableName','DBName','ReplacedDBName')
SELECT dbo.f_DBRenameX (dbo.f_DBRename ('Newdb.[DBName].dbo.TableName','DBName','ReplacedDBName'),'DBName','ReplacedDBName')
July 4, 2012 at 6:29 am
Here is my sp that i need to rename some server & db names.
create proc test
as
begin
select * from servername.dbname.dbo.tablename
end
Below is the sp that will replace the values according to the testenv.
--CREATE TABLE DBO.SP (ID INT IDENTITY (1,1), Name Varchar(100))
Create procedure DBO.sp_SPRename
AS
BEGIN
IF OBJECT_ID('SP') IS NOT NULL
DROP TABLE DBO.SP
--Creating a table and inserting the entire user created procedure names
Create table DBO.SP(ID INT IDENTITY(1,1), Name varchar(100))
INSERT INTO DBO.SP
select name from sys.objects where type = 'p' AND name not LIKE '%sp_SPRename%'
select * from SP
--Insert production & test values into dbo.tmp_Rename table
IF OBJECT_ID('dbo.tmp_Rename') IS NOT NULL
DROP TABLE dbo.tmp_Rename
Create table dbo.tmp_Rename (ID INT, ProductionName VARCHAR(100), TestName varchar(100) )
INSERT INTO dbo.tmp_Rename values('1','dbname','replaced_dbname')
INSERT INTO dbo.tmp_Rename values('2','servername','alteredservername')
IF OBJECT_ID('ToUpdate') IS NOT NULL
DROP TABLE DBO.ToUpdate
Create table DBO.ToUpdate(line varchar(8000))
IF OBJECT_ID('LastUpdate') IS NOT NULL
DROP TABLE DBO.LastUpdate
Create table DBO.LastUpdate(line varchar(8000))
DECLARE @MAXID INT
DECLARE @ID INT
DECLARE @SQL NVARCHAR(MAX)
DECLARE @RenameID INT
DECLARE @RenameMaxID INT
DECLARE @ProductionValue varchar(100)
DECLARE @OrigString VARCHAR(8000)
DECLARE @LookFor VARCHAR(100)
DECLARE @ReplaceWith VARCHAR(100)
declare @ddl varchar(8000)
SET @RenameID = 1
SET @ID = 1
SELECT @MAXID = MAX(ID) FROM SP
SELECT @RenameMaxID = MAX(ID) FROM dbo.tmp_Rename
WHILE (@ID<=@MAXID)
BEGIN
DECLARE @SPNAME VARCHAR(100)
SELECT @SPNAME = Name from SP WHERE ID = @ID
SET @SQL = 'insert ToUpdate exec sp_helptext '''+@SPNAME+''''
PRINT @SQL
EXEC SP_EXECUTESQL @SQL
select @OrigString = ''
select @OrigString = @OrigString + line
from ToUpdate
WHILE(@RenameID<=@RenameMaxID)
BEGIN
select @ProductionValue = ProductionName from dbo.tmp_Rename WHERE ID = @RenameID
select @Replacewith = Name from dbo.tmp_Rename WHERE ID = @RenameID
--WHILE (PATINDEX('% ' + @ProductionValue +'[^a-z]%', @OrigString) != 0)
--BEGIN
--SELECT @OrigString = STUFF(@OrigString, PATINDEX('%[^a-z]' + @ProductionValue +'[^a-z]%', @OrigString)+1, LEN(@ProductionValue), @Replacewith)
--END
--YOUR SUGGESSTION
Update ToUpdate set line = REPLACE(line,'''+@ProductionValue+''','''+@Replacewith+''')
SET @RenameID = @RenameID + 1
END
select @OrigString= REPLACE(@OrigString,'CREATE PROCEDURE','ALTER PROCEDURE')
select @OrigString= REPLACE(@OrigString,'CREATE PROC','ALTER PROC')
--select @ddl = ''
--select @ddl = @ddl + line
--from ToUpdate
SET @SQL = @OrigString
PRINT @SQL
Exec sp_executesql @SQL
SET @ID = @ID + 1
SET @RenameID = 1
TRUNCATE TABLE ToUpdate
END
END
Regards,
Karthik.
SQL Developer.
July 4, 2012 at 10:22 am
Thanks All for your solutions.
Regards,
Karthik.
SQL Developer.
July 4, 2012 at 11:57 pm
pls try belo code.
CLARE @OrigString VARCHAR(100)
DECLARE @SearchFor VARCHAR(100)
DECLARE @ReplaceWith VARCHAR(100)
SET @SearchFor ='.[Sample]'
SET @ReplaceWith = '.[Client_Sample]'
SET @OrigString = 'Newdb.[Sample].dbo.TableName'
select REPLACE(@OrigString, @SearchFor, @ReplaceWith)
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply