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

               (@SourceString VARCHAR(MAX),

                @FinalLength  INT,

                @PadChar      CHAR(1))

RETURNS VARCHAR(MAX)

AS

  BEGIN

    RETURN

      (SELECT Replicate(@PadChar,@FinalLength - Len(@SourceString)) + @SourceString)

  END

GO

 

 

REATE FUNCTION RPAD

               (@SourceString VARCHAR(MAX),

                @FinalLength  INT,

                @PadChar      CHAR(1))

RETURNS VARCHAR(MAX)

AS

  BEGIN

    RETURN

      (SELECT @SourceString + Replicate(@PadChar,@FinalLength - Len(@SourceString)))

  END

GO

 

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

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

SELECT NewString = dbo.LPAD(Cast(123 AS VARCHAR),6,'0')

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 (

   p_data   VARCHAR2

)

   RETURN NUMBER

IS

   v_number   NUMBER;

   o_bool     BOOLEAN := TRUE;

BEGIN

   BEGIN

      v_number := p_data;

   EXCEPTION

      WHEN OTHERS THEN

         o_bool := FALSE;

   END;

 

   RETURN o_bool;

END isnumber;

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 

 

rownum	data_srce_id
1	8
2	7
3	6
4	5
5	4
6	3
7	2
8	1
9	-1

 

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]

SELECT IDENTITY(int, 1,1) id

      ,t1.[column1]           col1

      ,t1.[column2]           col2

      ,t1.[column3]           col3

      ,t2.[column2]           col4

  INTO #Temp

  FROM [dbo].[Table] t1

 INNER JOIN [dbo].[Table2] t2

    ON t.[column] = t2.[column]

 

select *

  from #Temp

Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 491520 bytes) in /home1/golfcomp/public_html/rickcaminiti/wp-content/plugins/gd-star-rating/code/t2/templates.php on line 155