T-SQL Equivalent for PL-SQL

Question

T-SQL Equivalent for PL-SQL

Siva Ramasamy

SSCarpal Tunnel

Points: 4026
More actions
April 2, 2014 at 10:18 pm

#396530

Hi All,
Thanks for looking in to my question.
I need a T-SQL (SQL Server) equivalent code for the PL/SQL (Oracle) query given below.
Could you help?
select sum(position_value) from
(
select power(36,position-1) * case when digit between '0' and '9'
then to_number(digit)
else 10 + ascii(digit) - ascii('A')
end
as position_value
from (
select substr(input_string,length(input_string)+1-level,1) digit,
level position
from (select '01Z' input_string from dual)
connect by level <= length(input_string)
)
)
Thanks!
Siva.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

If you google around, it is easy to find the T-SQL equivalent functions. Is there anything in particular you're struggling with?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 2

Quick translation (Google Translate style 😀 )

;WITH NUMS(level) AS

(SELECT N FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS NM(N))

select sum(C.position_value) from

(

select

power(36,B.position-1) * case

when B.digit between '0' and '9' then CAST(B.digit AS INT)

else 10 + ascii(B.digit) - ascii('A')

end

as position_value

from

(

select

SUBSTRING(A.input_string,LEN(A.input_string)+1- dual.level,1) digit,

level position

from

(

select '01Z' AS input_string

) AS A

CROSS APPLY NUMS AS dual

WHERE level <= LEN(input_string)

) AS B

) AS C

Siva Ramasamy SSCarpal Tunnel Points: 4026 More actions · Answer 3

Thanks for spending your valuable time to answer my question.

Siva Ramasamy SSCarpal Tunnel Points: 4026 More actions · Answer 4

Thanks for looking into my question. I was not able to understand the usage of connect by statement. Let me try the answer given in the other reply.

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 5

Siva Ramasamy (4/3/2014)
Thanks for looking into my question. I was not able to understand the usage of connect by statement. Let me try the answer given in the other reply.

I haven't done a deep dive into the Oracle code you posted. What is it supposed to do? I ask because direct replacements between Oracle and SQL Server are frequently the worst thing that you can do on anything complex.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 6

Jeff Moden (4/3/2014)
What is it supposed to do?

It is a simple base36 to base10 number conversion.

01Z(base36) == 71 (base10)

😎

Siva Ramasamy SSCarpal Tunnel Points: 4026 More actions · Answer 7

One of my developers approached me with this query and I was not able to understand the Oracle Language.

Thanks for your help.