Scribd
Upload
12 views2 pages

Informatica Functions With Examples

The document provides a comprehensive overview of various string, numeric, date, conversion, conditional, aggregate, and miscellaneous functions used in programming. Each function is described with its purpose and an example of its usage. This serves as a reference guide for understanding and implementing these functions effectively.

Uploaded by

Bhäråth Kâtâbãthînî
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd
12 views2 pages

Informatica Functions With Examples

The document provides a comprehensive overview of various string, numeric, date, conversion, conditional, aggregate, and miscellaneous functions used in programming. Each function is described with its purpose and an example of its usage. This serves as a reference guide for understanding and implementing these functions effectively.

Uploaded by

Bhäråth Kâtâbãthînî
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd

String Functions

Function Descriptio Example


UPPER(striConverts stUPPER('hello') → 'HELLO'
LOWER(striConverts stLOWER('HELLO') → 'hello'
INITCAP(strCapitalizesINITCAP('hello world') → 'Hello World'
LTRIM(stri Removes leLTRIM(' hello') → 'hello'
RTRIM(striRemoves trRTRIM('hello ') → 'hello'
TRIM(strin Removes bo TRIM(' hello ') → 'hello'
SUBSTR(striExtracts suSUBSTR('Informatica', 1, 5) → 'Infor'
INSTR(strinReturns posINSTR('Informatica', 'ma') → 6
REPLACE(strReplaces s REPLACE('abc def', ' ', '_') → 'abc_def'
LPAD(stringPads stringLPAD('123', 5, '0') → '00123'
RPAD(stringPads stringRPAD('123', 5, '0') → '12300'
LENGTH(strReturns le LENGTH('abc') → 3

Numeric Functions
Function Descriptio Example
ROUND(numbRounds a ROUND(3.567, 2) → 3.57
CEIL(numbeRounds upCEIL(3.2) → 4
FLOOR(num Rounds doFLOOR(3.9) → 3
MOD(divideReturns re MOD(10, 3) → 1
ABS(numbeAbsolute vABS(-10) → 10
POWER(x, yx raised to POWER(2, 3) → 8
SQRT(numbSquare rooSQRT(16) → 4

Date Functions
Function Descriptio Example
SYSDATE Current sy SYSDATE → '06-JUL-2025'
CURRENT_Same as S —
TO_DATE(stConverts stTO_DATE('06-07-2025', 'DD-MM-YYYY')
TO_CHAR(da
Converts daTO_CHAR(SYSDATE, 'YYYY-MM-DD')
ADD_TO_DAT
Adds/subtrADD_TO_DATE(SYSDATE, 'DD', -7)
MONTHS_BEDifference MONTHS_BETWEEN(DATE1, DATE2)
LAST_DAY(Last day o LAST_DAY(SYSDATE)
NEXT_DAY(d
Next speci NEXT_DAY(SYSDATE, 'MONDAY')

Conversion Functions
Function Descriptio Example
TO_CHAR(eConverts toTO_CHAR(12345) → '12345'
TO_INTEGER
Converts toTO_INTEGER('123') → 123
TO_DECIMAConverts t TO_DECIMAL('123.45')
TO_DATE(stConverts stTO_DATE('2025-07-06', 'YYYY-MM-DD')
TO_FLOAT(e
Converts toTO_FLOAT('123.45')
Conditional Functions
Function Descriptio Example
IIF(conditi IF-ELSE logIIF(SALARY > 5000, 'High', 'Low')
DECODE(expr Like switchDECODE(GRADE, 'A', 90, 'B', 80, 0)
ISNULL(expChecks for ISNULL(COMM) → TRUE or FALSE
NVL(expr1,Replace nulNVL(COMM, 0)
NULLIF(expReturns nulNULLIF(SALARY, 0)

Aggregate Functions
Function Descriptio Example
SUM(expresTotal SUM(SALARY)
AVG(expresAverage AVG(SALARY)
MAX(expresMaximum vMAX(SALARY)
MIN(expresMinimum vMIN(SALARY)
COUNT(expNumber ofCOUNT(EMP_ID)
MEDIAN(exMedian MEDIAN(SALARY)

Miscellaneous Functions
Function Descriptio Example
SETVARIABLE
Sets mappiSETVARIABLE('$$MAX_SAL', SALARY)
GETVARIABL
Gets mappiGETVARIABLE('$$MAX_SAL')
ERROR('msg
Stops proceERROR('Invalid record')
UUID() Generates Uu UID() → 'a1b2c3d4-...'

You might also like