Archive

Posts Tagged ‘equivalents’

Rpad and Lpad Equivalent in T-SQL

June 16th, 2009 NothingMan No comments

 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/

VN:F [1.5.8_856]

Rating: 0.0/5 (0 votes cast)
VN:F [1.5.8_856]
Rating: 0 (from 0 votes)
Categories: BI, Code, Tips Tags: , , , , , , ,

Generate ROWNUM pseudo-column in SSIS

May 27th, 2009 NothingMan No comments

I posted earlier on how to generate a rownumber in SQL 2000, but that would not work as a source query in SSIS for some reason.  Without having to create a stored procedure or temp tables based on other temp tables, I wanted a simple way to generate this rownum in SSIS.  I was considering something with variable expressions, but then I found a way to do it with a Script Component.

Drag a Script Component Task into your DataFlow and connect it between your source and destination tasks.  Open it up and click “Inputs and Outputs”.  Double Click “Output 0″ and highlight “Output Columns” and click Add Column.  For this code to work, name that new column “rownum“.

Then open the script tab and click “Design Script” and paste this code over what is already there:

  

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

 

Public Class ScriptMain

    Inherits UserComponent

    Dim counter As Integer = 0

 

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

 

        Row.rownum= counter

        counter = counter + 1

    End Sub 

End Class

 

VN:F [1.5.8_856]

Rating: 5.0/5 (1 vote cast)
VN:F [1.5.8_856]
Rating: 0 (from 0 votes)
Categories: BI, Code, Tips Tags: , , ,

Javascript urlencode() Equivalent

May 9th, 2009 NothingMan No comments

This is a rather strange topic for a Business Intelligence blog, and I’ll probably forever kick myself for making it my first post, but nonetheless, I ran into an issue today trying to get around some ridiculous BO messes. One of which was that my hacks had to all be in Javascript. The other was that I had to go back and forth between POSTing and GETing http variables.

Without getting into any more ugly details, the bottom line is that I always knew there was a nifty little PHP function called urlencode(), but I didn’t know exactly what was available in Javasctipt. Thanks to a quick look on phpbuilder, I realized that there was a built-in function simply called escape() that would work just fine. I needed this because I was building a query string (&abc=1&xyz=2&string=blah) but since I was flipping back and forth between GET and POST methods, I needed to encode it.

The escape() function actually worked pefectly for me, but as a side-note, as cass-hacks explains, there is a distinc difference between urlencode() and escape(), and this would be a way to mimic it more accurately:


function urlencode(str) {
return escape(str).replace('+', '%2B').replace('%20', '+').replace('*', '%2A').replace('/', '%2F').replace('@', '%40');
}


function urldecode(str) {
return unescape(str.replace('+', ' '));
}

VN:F [1.5.8_856]

Rating: 2.0/5 (1 vote cast)
VN:F [1.5.8_856]
Rating: 0 (from 0 votes)
Categories: Code Tags: , , , ,

Instr() Equivalent in SQL Server

May 8th, 2009 NothingMan 6 comments

While delving into the wonderful (*ahem) world of SQL server, I was attempting to parse through some text fields and needed to find an alternative to the Oracle instr() function.

The syntax for the INSTR Oracle function is:


instr( string1, string2 [, start_position [, nth_appearance ] ] )

string1 is the string to search.

string2 is the substring to search for in string1.

start_position is the position in string1 where the search will start. This argument is optional. If omitted, it defaults to 1. The first position in the string is 1. If the start_position is negative, the function counts back start_position number of characters from the end of string1 and then searches towards the beginning of string1.

nth_appearance is the nth appearance of string2. This is optional. If omitted, it defaults to 1.

The syntax for CHARINDEX in T-SQL is


CHARINDEX ( expression1 ,expression2 [ , start_location ] )

expression1 Is a character expression that contains the sequence to be found. expression1 is limited to 8000 characters.

expression2 Is a character expression to be searched.

start_location Is an integer or bigint expression at which the search starts. If start_location is not specified, is a negative number, or is 0, the search starts at the beginning of expression2.

VN:F [1.5.8_856]

Rating: 4.0/5 (2 votes cast)
VN:F [1.5.8_856]
Rating: 0 (from 0 votes)
Categories: BI, Code, Tips Tags: , , , , ,

Getting Table/Column Metadata in SQL Server

May 7th, 2009 NothingMan No comments

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