function

Oracle/SQL CONCAT/CONCATENATE Function

November 23, 2012 by techhoneyadmin

Oracle/SQL CONCAT Function is used to concatenate two strings to form one string. The return type of SQL CONCAT Function is a string. Syntax and examples of Oracle CONCAT Function are mentioned below.

SQL CONCAT Function Syntax

SELECT CONCAT(string_1, string_2)
FROM table_name;

string_1 and string_2 are the strings to be concatenated.

SQL CONCAT Function Example – Use with SQL SELECT Statement

Oracle CONCAT Function is used with the SQL SELECT Statement.

For example, the Oracle CONCAT Function query below returns a string by concatenating two strings.

SELECT CONCAT('TECH', 'HONEY') "Oracle CONCAT Function"
FROM dual;

The above Oracle CONCAT Function query returns ‘TECHHONEY’.

Note: We have aliased CONCAT(‘TECH’, ‘HONEY’) as Oracle CONCAT Function

SQL CONCAT Function – Concatenating Strings having Whitespaces Example

SQL CONCAT Function can also be used to concatenate strings having whitespaces.

For example, the Oracle CONCAT Function query below returns a string after joining two string, one with spaces at the end.

SELECT CONCAT('TECH ', 'HONEY')
FROM dual;

The above Oracle CONCAT Function query returns ‘TECH HONEY’.

SQL CONCAT Function – Concatenating String Having Whitespaces Example

SQL CONCAT Function can also concatenate strings which have white space in between the words.

For example, the below Oracle CONCAT Function query returns a string after joining two string having whitespace at end and in between strings.

SELECT CONCAT('ORACLE ', 'SQL PLSQL')
FROM dual;

The above CONCAT Function query returns ‘ORACLE SQL PLSQL’.

Oracle COMPOSE Function

November 23, 2012 by techhoneyadmin

Oracle COMPOSE Function is used to get the Unicode string.Oracle COMPOSE Function is used with the SQL SELECT Statement.

Oracle COMPOSE Function Syntax

SELECT COMPOSE (string)
FROM table_name;

string is the input for the COMPOSE function and is used to create the Unicode string and it can be CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB type.

Table below shows Uni-string values that can combine with other characters via Oracle COMPOSE Function.

Unistring Values	Resulting character
UNISTR(‘\0300’)	grave accent ( ` )
UNISTR(‘\0301’)	acute accent ( ´ )
UNISTR(‘\0302’)	circumflex ( ^ )
UNISTR(‘\0303’)	tilde ( ~ )
UNISTR(‘\0308’)	umlaut ( ¨ )
UNISTR(‘\0300’)	grave accent ( ` )
UNISTR(‘\0301’)	acute accent ( ´ )
UNISTR(‘\0302’)	circumflex ( ^ )

Oracle COMPOSE Function – Combining with Uni-string Examples

Oracle COMPOSE Function examples below show combining Uni-String values with other characters.

For example, the below SQL query returns combination of ‘a’ with UNISTR(‘\0308’).

SELECT COMPOSE('a' || UNISTR('\0308'))
FROM dual;

Will return ‘ä’

For example, the below SQL query returns combination of ‘e’ with UNISTR(‘\0300’).

SELECT COMPOSE('e' || UNISTR('\0300'))
FROM dual;

Will return ‘è’

For example, the below SQL query returns combination of ‘e’ with UNISTR(‘\0302’).

SELECT COMPOSE('e' || UNISTR('\0302'))
FROM dual;

Will return ‘ê’

CHR Function in Oracle SQL – PLSQL

November 23, 2012 by techhoneyadmin

The CHR function in Oracle SQL / PLSQL is used to get the character from the ASCII value code. CHR function is basically opposite of the ASCII function in Oracle SQL / PLSQL

Syntax for the CHR Function in Oracle SQL / PLSQL is:

SELECT CHR (ascii_number)
FROM table_name;

Examples:

Using CHR function in Oracle SQL / PLSQL SELECT statement:

SELECT CHR('97')
FROM dual;

Will return ‘a’

SELECT CHR (65)
FROM dual;

Will return ‘A’

SELECT CHR (98)
FROM dual;

Will return ‘b’

Oracle ASCIISTR Function

November 23, 2012 by techhoneyadmin

Oracle ASCIIStr Function is used to convert a string in any character set to ASCII String. Syntax and examples of using the ASCIIStr Function are listed below.

Oracle ASCIISTR Function Syntax

SELECT ASCIIStr (string)
FROM table_name;

Oracle ASCIISTR Function – Use with SQL SELECT Statement Example

Oracle ASCIISTR Function is used with the SQL SELECT Statement.

For example, the SQL ASCIISTR Function query below returns the ASCII String value of the string passed.

SELECT ASCIIStr ('Tech Honey')
FROM dual;

The above SQL SELECT Query will return ‘Tech Honey’ as the ASCII String value for ‘Tech Honey’

Oracle ASCIISTR Function – Use with Special Characters Example

Oracle ASCIISTR Function can be used with special characters.

For example, the SQL ASCIISTR Function query below returns the ASCII String format for the string passed.

SELECT ASCIIStr ('Tech Honey Ä Ê Õ Ø')
FROM dual;

The above SQL SELECT Query will return ‘Tech Honey \00C4 \00CA \00D5 \00D8’ as the ASCII String value for ‘’Tech Honey Ä Ê Õ Ø’’

Oracle/SQL ASCII Function

November 23, 2012 by techhoneyadmin

Oracle ASCII Function is used to get the NUMBER code that represents the character in ASCII. In other words, SQL ASCII Function returns the ASCII number of the character passed to it.

Oracle ASCII Function Syntax

SELECT ASCII (character)
FROM table_name;

In the above Oracle/SQL ASCII Function Syntax:-

character is a single character that is passed to the ASCII function.
If more than 1 character is passed to the ASCII function then ASCII function will return the ASCII value of the first character and ignore other characters.

Oracle ASCII Function – Using SQL SELECT Statement Example 1

Oracle ASCII Function is used with the SQL SELECT Statement to get the ASCII value of the character passed.

For example, the SQL ASCII Function query below returns the ASCII value of ‘a’.

SELECT ASCII ('a')
FROM dual;

Will return 97

Oracle ASCII Function – Using SQL SELECT Statement Example 2

The below SQL ASCII Function query returns the ASCII value of ‘abc’.

SELECT ASCII ('abc')
FROM dual;

Will also return 97 and ignore the ASCII values of b and c characters.

Oracle ASCII Function – Using SQL SELECT Statement Example 3

The below SQL ASCII Function query returns the ASCII value of ‘A’.

SELECT ASCII ('A')
FROM dual;

Will return 65