asked on

Help to convert a PostgreSQL query into Microsoft SQL Server syntax

Hi there,

I have this KB article from Atlassian, and they show a specific PostgreSQL query, but I need to run it against Microsoft SQL server. Anybody able to assist?

The article is: https://confluence.atlassian.com/confkb/some-attachments-or-links-are-no-longer-accessible-after-server-migration-214862724.html

The query itself that is shown is:

insert into contentmigration
select bodycontentid, substring(body from '\\|http://oldServerName/download/attachments/.*/') from bodycontent;

Thanks for any suggestions!

PortletPaul

is it the "body from" function you are asking about or the equivalent of insert into?

Pawan Kumar

There is not direct function available for this in SQL Server ... can you provide us what you are getting from that substring ( bodu from ???

Greg Besso

ASKER

The body substring is the main part i'f like assistance, but the whole thing is needed (I assume he insert portion is the easy part?

Pawan Kumar

Seems like we need to use a web service method to get data from the URL.
Please like this.. You may need to modify few things....

CREATE PROC CallWebService
(
	 @strSerialNumber VARCHAR(50)
	,@strPassword VARCHAR(50)
	,@strUserID VARCHAR(50)
	,@strPostcode VARCHAR(50)
	,@strProperty VARCHAR(50)
	,@strCountyType VARCHAR(50)
)
AS
BEGIN

	Declare @Ob as Int, @ReturnData as Varchar(MAX);
	DECLARE @WebU AS VARCHAR(MAX) = 'http://oldServerName/download/attachments/'
	SET @WebU = @WebU + @strSerialNumber + '&strPassword=' + @strPassword + '&strUserID=' + @strUserID + '&strPostcode=' + @strPostcode + '&strProperty=' + @strProperty + '&strCountyType=' + @strCountyType
	Exec sp_OACreate 'MSXML2.XMLHTTP', @Ob OUT;
	Exec sp_OAMethod @Ob, 'open', NULL, 'get', @WebU,'false'
	Exec sp_OAMethod @Ob, 'send'
	Exec sp_OAMethod @Ob, 'responseText', @ReturnData OUTPUT
	Select @ReturnData
	Exec sp_OADestroy @Ob

END

Open in new window

Greg Besso

ASKER

NO what I need is to just get the query working exactly as written. Not looking for content from the URL at all. The URL is in a body / text area of a wiki page. The script is just finding records where that URL is referenced for use later on.

Pawan Kumar

So direct conversion is not possible in SQL SERVER ( T-SQL )

Greg Besso

ASKER

There must be a way to just nest some instring type of function where this substring of the column is right?

Pawan Kumar

Yes we have CHARINDEX for that.

https://social.technet.microsoft.com/wiki/contents/articles/17948.t-sql-right-left-substring-and-charindex-functions.aspx

Pawan Kumar

can you tell me what it this doing. I mean the output of below...

substring(body from '\\|http://oldServerName/download/attachments/.*/')
from table..

then i can rewrite in SQL Server..

Also can you please provide few rows from the input table you have.

Greg Besso

ASKER

If you can check out that URL it has a 2 or 3 step process I'd be performing.

Pawan Kumar

cool.. Can you get me few rows in excel from the table and the output
of below for those rows i can rewrite the query for you in MS SQL Server.

select bodycontentid, substring(body from '\\|http://oldServerName/download/attachments/.*/') from bodycontent;

Greg Besso

ASKER

OK got a couple records as reference (see attached here). Thanks again!
example.csv

Pawan Kumar

not clear, could you please use xlsx for some data from the table and the output of the query..

Greg Besso

ASKER

It's HTML in the one column.

Pawan Kumar

Ok....
can you give me output of below..

select substring(body from '\\|http://oldServerName/download/attachments/.*/')
from bodycontent
WHERE bodycontentid = 458758;

Greg Besso

ASKER

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'from'.

Greg Besso

ASKER

In cause useful, the table design info...

Pawan Kumar

select substring(body from '\\|http://oldServerName/download/attachments/.*/')
from bodycontent
WHERE bodycontentid = 458758;

you need to run the above in postgresql..

Greg Besso

ASKER

I don't have a PostgreSQL instance. I want to run the query in Microsoft SQL Server, which is where our database is stored.
That is the goal of this question.

Pawan Kumar

ok... you have MS SQL table....

Can you send some rows from it and the EXPECTED OUTPUT you need ..

Greg Besso

ASKER

I don't exactly know. Basically i'm trying to export the rows into a new TEMP table, then if you read the KB article I posted to begin with it shows the next steps. All it's doing is overwriting a portion of the HTML to restore the correct URL from one table into the existing content in the destination table.

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.