1

I need to translate the Oracle SQL-code

select URL, TO_NUMBER(NVL( substr(URL,INSTR(URL,'pageId=') + 7, INSTR(URL,'&')- (INSTR(URL,'pageId=') + 7) )  , substr(URL,INSTR(URL,'pageId=') + 7) )) PageId
            from remotelink
           WHERE INSTR(URL,'pageId')>0;

to T-SQL (MS SQL-Server 2012)

My translated Code looks like this:

select URL, CAST(ISNULL( substring(URL,CHARINDEX('pageId=', URL) + 7, CHARINDEX('&', URL)- (CHARINDEX('pageId=', URL) + 7) )  , substring(URL,CHARINDEX('pageId=', URL) + 7, datalength(URL)) )AS numeric) PageId
            from JIRA_TEST.jiraschema.remotelink
           WHERE CHARINDEX('pageId', URL) >0

I receive the error

"invalid length parameter passed to the left or substring function"

Any hints?

bent
  • 11
  • 2
  • 1
    Welcome to stackoverflow! Actually, sample data and expected results would be a much better help for us to help you solve your problem. Please [edit] your question to follow the guidlines in the first three paragraphs of the [tsql tag info](https://stackoverflow.com/tags/tsql/info). – Zohar Peled Aug 20 '19 at 08:05

0 Answers0