how to change position of a hardcode value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to change position of a hardcode value

Author

Topic

shlok_sql_learn
Starting Member

6 Posts

Posted - 2010-03-25 : 12:56:50

Hello,

I have queries which were written for oracle earliern.
Select Name,GroupName from Products##SOURCEDB## WHERE NAME IS NOT NULL

Select ADDRESS1,ADDRESS2,ADDRESS3 from ADDRESS##SOURCEDB## ADS
inner join ADDSS_HIST##SOURCEDB## ADH ON ADS.ADNO = ADH.ADNO
AND ADDSS IS NOT NULL

Queries were written for Oracle using ANSI standards. Based on the source db name, the hard coded value ##SOURCEDB## will be replaced to oracle server name.
After replace the output is

Select Name,GroupName from Products@PRODSERVER WHERE NAME IS NOT NULL

Select ADDRESS1,ADDRESS2,ADDRESS3 from ADDRESS@PRODSERVER ADS
inner join ADDSS_HIST ADH ON ADS.ADNO = ADH.ADNO
AND ADDSS IS NOT NULL

I need your opinion on how to prefix the hardcoded value before the table name.
Example:

Select Name,GroupName from ##SOURCEDB##Products WHERE NAME IS NOT NULL

Select ADDRESS1,ADDRESS2,ADDRESS3 from ##SOURCEDB##ADDRESS ADS
inner join ##SOURCEDB##ADDSS_HIST ADH ON ADS.ADNO = ADH.ADNO
AND ADDSS IS NOT NULL

Thanks to all in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-25 : 13:00:11

you need to use dynamic sql for that

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

shlok_sql_learn
Starting Member

6 Posts

Posted - 2010-03-26 : 11:45:37

Hi Visakh,

Thanks for reply.

I am new to sql and i don't know much. I think that dynamic sql are using for executing a string that is created dynamically.

My requirement is just to move one word in my sql statement to different position.

Ex:
Select Name,GroupName from Products##SOURCEDB## WHERE NAME IS NOT NULL

After processing
Select Name,GroupName from ##SOURCEDB##Products WHERE NAME IS NOT NULL

Please guide me on how to start with.

thanks

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-26 : 12:12:34

If ##SOURCEDB## is a variable, then you need to create a string and execute it, like this:


DECLARE @SQL VARCHAR(8000)

SET @SQL = 'Select Name,GroupName from ' + @SourceDB + '.dbo.Products WHERE NAME IS NOT NULL'

EXEC (@SQL)

However, if ##SOURCEDB## is a hard coded database name, then you can just do this:


Select Name,GroupName from SourceDB.dbo.Products WHERE NAME IS NOT NULL

Both these examples assume the table Products is in the dbo schema.

There are 10 types of people in the world, those that understand binary, and those that don't.

shlok_sql_learn
Starting Member

6 Posts

Posted - 2010-03-26 : 12:22:04

A small example to explain my requirement...

Declare @MyLocalTable Table
(
QueryNo int Identity,
QueryUsedInOracle varchar(Max)
)

Insert into @MyLocalTable
Values ('Select Name,GroupName from Products##SourceDB## WHERE NAME IS NOT NULL')

Insert into @MyLocalTable
Values ('Select DeptNo,DeptName from Dept##SourceDB## WHERE NAME IS NOT NULL')

-- and Insert more hundreds of queries which were written originally for oracle using ANSI std.

Output I need:
--After doing the processing suggested by you

Select * from @MyLocalTable --Should return
1,'Select Name,GroupName from ##SourceDB##Products WHERE NAME IS NOT NULL'
2,'Select DeptNo,DeptName from ##SourceDB##Dept WHERE NAME IS NOT NULL'

I know the requirement is something strange so request you experts to guide me on this.

thanks.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-26 : 13:18:54

Here's a script that will convert "Product##SOURCEDB##" to "##SOURCEDB##Product" for a given string. You could build this into a function, and pass to it in your select statement.




DECLARE @SQL VARCHAR(8000)
DECLARE @Pos1 INT
DECLARE @Pos2 INT
DECLARE @Head VARCHAR(8000)
DECLARE @Tail VARCHAR(8000)

SET @SQL = 'Select Name,GroupName from Products##SourceDB## WHERE NAME IS NOT NULL'
-- Locate the position of the first ## 
SET @Pos1 = PATINDEX ( '%##%## %' , @SQL )

IF @Pos1 > 1
BEGIN
	-- Split the string
	SET @Head = LEFT(@SQL, @Pos1 - 1)
	SET @Tail = SUBSTRING(@SQL, @Pos1, 8000)

	-- Find the end of ##SOURCEDB##
	SET @Pos1 = PATINDEX ( '% %' , @Tail ) 
	
	-- Copy ##SOURCEDB## into source, before 
	SET @Pos2 = LEN(@Head)
	WHILE SUBSTRING(@Head, @Pos2 - 1, 1) NOT IN (' ', CHAR(7), CHAR(10))
		SET @Pos2 = @Pos2 - 1 
	
	SET @SQL = STUFF(@Head, @Pos2, 0, SUBSTRING(@Tail, 1, @Pos1 - 1)) + SUBSTRING(@Tail, @Pos1, 8000)
END

PRINT @SQL

There are 10 types of people in the world, those that understand binary, and those that don't.

shlok_sql_learn
Starting Member

6 Posts

Posted - 2010-03-27 : 05:11:16

quote:

Originally posted by DBA in the making

Here's a script that will convert "Product##SOURCEDB##" to "##SOURCEDB##Product" for a given string. You could build this into a function, and pass to it in your select statement.




DECLARE @SQL VARCHAR(8000)
DECLARE @Pos1 INT
DECLARE @Pos2 INT
DECLARE @Head VARCHAR(8000)
DECLARE @Tail VARCHAR(8000)

SET @SQL = 'Select Name,GroupName from Products##SourceDB## WHERE NAME IS NOT NULL'
-- Locate the position of the first ## 
SET @Pos1 = PATINDEX ( '%##%## %' , @SQL )

IF @Pos1 > 1
BEGIN
	-- Split the string
	SET @Head = LEFT(@SQL, @Pos1 - 1)
	SET @Tail = SUBSTRING(@SQL, @Pos1, 8000)

	-- Find the end of ##SOURCEDB##
	SET @Pos1 = PATINDEX ( '% %' , @Tail ) 
	
	-- Copy ##SOURCEDB## into source, before 
	SET @Pos2 = LEN(@Head)
	WHILE SUBSTRING(@Head, @Pos2 - 1, 1) NOT IN (' ', CHAR(7), CHAR(10))
		SET @Pos2 = @Pos2 - 1 
	
	SET @SQL = STUFF(@Head, @Pos2, 0, SUBSTRING(@Tail, 1, @Pos1 - 1)) + SUBSTRING(@Tail, @Pos1, 8000)
END

PRINT @SQL

There are 10 types of people in the world, those that understand binary, and those that don't.

Thank you very much sir..

I tried lot of ways but was unsuccessful.
Your scripts is giving me the expected result.

Thanks you again.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-27 : 08:24:24

quote:
Originally posted by shlok_sql_learn
Thank you very much sir..

I tried lot of ways but was unsuccessful.
Your scripts is giving me the expected result.

Thanks you again.

No problemo. Just leave your money on the fridge. :)

A more efficient way of doing it would be to use CLR and Regex, but that's a little complex. This solution is a little slower (probably a lot slower), but much easier to implement.

There are 10 types of people in the world, those that understand binary, and those that don't.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources