implementing rpad in sqlserver

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

General SQL Server Forums

New to SQL Server Programming

implementing rpad in sqlserver

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-02-19 : 12:47:16

select rpad('1234',10) from dual --oracle

SELECT LEFT( LTRIM(RTRIM('1234'))+SPACE(10), 10 )--sqlserver

is it good to go with this code for rpad in sqlserver.
10 is hardcoded in code in oracle, I am migrating one function in oracle which has rpad in it, to sqlserver

any idea.

Regards,
Asif

Kristen
Test

22859 Posts

Posted - 2010-02-19 : 13:10:32

Looks all right to me, but I would leave RTRIM() out - if there are already spaces on the right no need to remove them ... and then add them back in again!

Does Oracle RPAD() do an LTRIM() first? Would " 1234" be output as "1234......" or ".1234....." ?

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-02-19 : 13:30:09

Thanks Kirsten, looks good rtrim not needed.

oracle rpad,


select rpad('1234',3) from dual
='123'
select rpad('123',3) from dual
='123'
select rpad('12',3) from dual
='12 '
select rpad('1',3) from dual
='1  '

select rpad('  1',3) from dual
'  1'

means Oracle Rpad do not do ltrim.

Kristen
Test

22859 Posts

Posted - 2010-02-19 : 13:31:58

"means Oracle Rpad do not do ltrim"

OK then leave LTrim out too in SQL Server function I think??

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-02-19 : 14:57:11

Thanks again! Kristen,

SQL does not have if elsif elsif ... contruct? I think need to use IF else if else...

Regards,
aak

Kristen
Test

22859 Posts

Posted - 2010-02-20 : 01:39:25

SQL's IF/ELSE is CASE

SELECT CASE WHEN A=B THEN This WHEN C = D THEN That ELSE TheOther END

or

SELECT CASE ColumnOrExpression WHEN 'A' THEN This WHEN 'B' THEN That ELSE TheOther END

(There is also IF / ELSE / END available for use in procedural steps - for example in a stored procedure, but CASE is what you need within a SELECT statement)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-20 : 01:51:11

quote:
Originally posted by aakcse

select rpad('1234',10) from dual --oracle

SELECT LEFT( LTRIM(RTRIM('1234'))+SPACE(10), 10 )--sqlserver

is it good to go with this code for rpad in sqlserver.
10 is hardcoded in code in oracle, I am migrating one function in oracle which has rpad in it, to sqlserver

any idea.

Regards,
Asif

Why do you need this?
Are you exporting data to fixed length file?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources