Rick Caminiti dotCOM

 Somehow, there’s no built-in function equivalent to Oracle’s LPAD or RPAD in SQL.  There are several ways to do this, in fact, I posted another way to do RPAD at http://rickcaminiti.com/bi/t-sql-equivalent-of-oracles-rpad/.  Unfortunately, this leaor
ves no way to do an LPAD.

So here’s the scenario:  You have, let’s say an account number that is stored as a number in one system and as a char in another system with leading zeros.  So, 123 might look like 000123 in the other system.  So if you need to represent 123 as 000123, you need to turn it into a char(6) and add 3 zeros to the begining.  That’s exactly what this LPAD function would do. 

CREATE FUNCTION LPAD

GO

 In the example above, you would accomplish this like so:

EXEC dbo.LPAD(Cast(123 AS VARCHAR),6,'0')
OR

Here’s where I found it. http://www.sqlusa.com/bestpractices2005/padleadingzeros/

Short and sweet. Accept a varchar input parameter, try to convert it to a number. If the conversion fails for any reason, return FALSE, else, return TRUE.

CREATE OR REPLACE FUNCTION is_numeric (

Oracle has a pseudo-column called ROWNUM that allows you to display the row number for each row.  There are infinite reasons why you might want to use the ROWNUM, but in SQL it’s not nearly as easy to determine.

In Oracle, it’s simply

SELECT ROWNUM from table_name

In SQL 2005+, there is actually a ROW_NUMBER() function, but you have to give it some more information to use it.

ROW_NUMBER ( )     OVER ( [ <partition_by_clause> ] <order_by_clause> )

For example:
use [Database]
select row_number() over (order by data_srce_id desc) rownum

      ,data_srce_id
from data_srce 

However, in SQL 2000 it’s a little more difficult.  The only way I’ve found is using the IDENTITY() function, which requires an INTO statement, so you pretty much have to load the results into a #temporary table and then select from the #temporary table like this:

use [Database]

  from #Temp

Ok, well, this might now be the “equivalent”, and I don’t yet have a solution for LPAD without using a Custom Function, but here’s what I did to perform an RPAD for something I was doing.

I had 2 int fields that I needed to concatenate together. The max size on both were 3 digits, so I decided to make my joint combination of the two keys both 3 digits so keep it as a smart key, always knowing the first 3 meant something and the last 3 meant something in the source databases. Again, these were ints, and I need the combination of the two to be ints.
Forgive the formatting, I’m TRYING to make it easier to read:

select
  cast(
   replace( /*replace spaces with 0s*/
    cast([col1] as nchar(3)) /*casting as nchar to provide training spaces*/
    , ' '
    , '0') +
   replace(
    cast(isnull(col2)], 0) as nchar(3)) --second field is nullable (left join), make them 0s
    , ' '
    , '0')
  as int) /*casting it back to an int*/
from table