Data Cleansing Functions

Hi all,

In My First Post I Would Like to Deliver My Views on Data Cleansing Functions of Powercenter.

1.LTrim:

LTRIM removes a specified set of characters from the left of a string. If no character set is specified,LTRIM defaults to a single blank.

SQL> -- Remove zeros from the left

SQL> -- of the string till it reaches

SQL> -- a non-zero character

SQL> select LTRIM('000102.3', '0')

2 from dual;

LTRIM

-----

102.3

So Trim Function Removes a Specified Set of Characters Until it Finds a Unspecified Character. (Here in Above Example Not Zero)

2.SubStr:

The Oracle SUBSTR SQL Function is very common, and many times misused.Below shows the SUBSTR function along with the arguments it takes:

substr(string, POSITION)

substr(string, POSITION, substring_length)

The Oracle SUBSTR function returns a portion of string, beginning at character position, substring_length characters long.

The Following are important rules to follow along with syntax exemplifying the implications of the rules.

A) The first character of string is at position 1. If position is 0, then it is treated as 1

EX:

SELECT substr('ravi',1) FROM dual;
--Returns: ravi
SELECT substr('ravi',0) FROM dual;
--Returns: ravi

B)If position is positive, then Oracle Database counts from the beginning of string to find the first character; if position is negative, then Oracle counts backward from the end of string.

SELECT substr('ravi',2) FROM dual;
--Returns: avi
SELECT substr('ravi',-2) FROM dual;
--Returns: vi

C) If substring_length is omitted, then Oracle returns all characters to the end of string. If substring_length is less than 1, then Oracle returns null.

SELECT substr('ravi',2) FROM dual;
--Returns: avi
SELECT substr('ravi',2,1) FROM dual;
--Returns: a
SELECT substr('ravi',2,0) FROM dual;
--Returns: [null]

3.INSTR:

The Oracle INSTR SQL function is popular and performs materially the same operation as

instr functions in many other programming languages.

Below shows the INSTR function along with the arguments it takes:

Instr(string, SUBSTRING)

Instr(string, SUBSTRING, start_position)

Instr(string, SUBSTRING, start_position, occurrence)

The Oracle INSTR function returns the position (an integer) within string of the first character in substring that was found while using the corresponding start_position and occurrence.

Following are important rules to follow along with syntax exemplifying the implications of the rules.

The first character of string is at start_position 1. start_position is defaulted as 1. If start_position is set to 0, 0 will always be returned,and thus, is not a useful value. If start_position is negative, searching for substring will begin at start_position characters counted from the end (right) of string and searching will be conducted towards the start (right-to-left) of string.

SELECT instr('ravikumar','v') FROM dual;

--Returns: 3

SELECT instr('ravikumar','r',1) FROM dual;

--Returns: 1

SELECT instr('ravikumar','r',2) FROM dual;

--Returns: 9

SELECT instr('ravikumar','r',0) FROM dual;

--Returns: 0

SELECT instr('ravikumar','r',-1) FROM dual;

--Returns: 9

SELECT instr('ravikumar','r',-4) FROM dual;

--Returns: 1

In My Next Post I Will Post The Combined Usage Of Substr&Instr and Other Cleansing Functions.