Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

Concatenate Strings Using || Operator in Oracle SQL PLSQL

by

The || operator in Oracle SQL / PLSQL can be used to concatenate multiple strings to form a single string.

Syntax for the using the || operator in Oracle SQL / PLSQL is:

SELECT string_1 || string_2 || string_3 || . . . || string_N
FROM table_name;

string_1, strring_2 . . string_N are the strings to be concatenated.

Examples:

Using || operator in Oracle SQL / PLSQL SELECT statement:

SELECT 'MONDAY '|| 'TUESDAY '|| 'WEDNESDAY '
FROM dual;

Will return ‘MONDAY TUESDAY WEDNESDAY ‘

SELECT 'TECH' || 'HONEY' || '.COM'
FROM dual;

Will return ‘TECHHONEY.COM’

Oracle/SQL CONCAT/CONCATENATE Function

by

Oracle CONCATENATE FunctionOracle/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

by

Oracle COMPOSE FunctionOracle 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

by

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

by

Oracle ASCIIStr FunctionOracle 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 Ä Ê Õ Ø’’