LPAD (Oracle) convert to equivalent in SQL 1

[outofservice]

Here is some Oracle PLsql that needs to be converted to its SQL Server equivalent. I am at a loss as to how to do this!!

Oracle SQL is:

SELECT lpad(n_num+600000, greatest(length(n_num+600000), length(n_num)),0) FROM numbr;

It takes the n_num column from the numbr table and increases the number by the set amount.

eg n_num
00123 + 8 = 00131
00123 + 60 = 00183
00123 + 600 = 00723
00123 + 6000 = 06123
00123 + 60000 = 60123
00123 + 600000 = 600123

But if the number is prefixed by 0's (zero's) it puts them back on, or if the end number is the same length as the original numbers length, with any prefix, it does not put the prefix on. The same for if the number goes above the original length.

Thanks for your help! Lauryn Bradley
SQL Server DBA

 

[sunila7]

Hi,

Try this ...

select len(cast(cast(fieldname as int) + n_num as varchar)),len(fieldname),
CASE
When len(cast(cast(fieldname as int) + n_num as varchar)) < len(fieldname)Then
replicate(0, len(fieldname)-len(cast(cast(fieldname as int) + n_num as varchar))) + cast(cast(fieldname as int) + n_num as varchar)
Else
cast(cast(fieldname as int) + n_num as varchar)
End
From TBL


Sunil

 

[sguslan]

Use str function:
SELECT REPLACE(STR(n_num+600000, 10, 0), ' ', '0') FROM ...

or use replicate

select replicate('0',len(cast(n_num+600000 as varchar)))+cast( n_num+600000 as varchar) FROM tbl; ________________________________________________________________________________
If you do not like change, get out of the IT business...