String/Character Manipulation functions in Oracle PL/SQL, Apache Hive

by | Jun 5, 2024 | SQL | 0 comments

Function NameDescriptionExample UsageResult
CONCATConcatenates two strings.SELECT CONCAT(‘Oracle’, ‘PL/SQL’) FROM dual;OraclePL/SQL
`` (Concatenation)Concatenates two strings.
LENGTHReturns the length of a string.SELECT LENGTH(‘Oracle’);6
LOWERConverts all characters in a string to lowercase.SELECT LOWER(‘ORACLE’);oracle
UPPERConverts all characters in a string to uppercase.SELECT UPPER(‘oracle’);ORACLE
INITCAPCapitalizes the first letter of each word in a string.SELECT INITCAP(‘oracle pl/sql’);Oracle Pl/Sql
SUBSTRReturns a substring from the specified position.SELECT SUBSTR(‘OraclePLSQL’, 7, 5) FROM dual;PLSQL
INSTRReturns the position of the first occurrence of a substring.SELECT INSTR(‘OraclePLSQL’, ‘PL’) FROM dual;7
REPLACEReplaces all occurrences of a substring within a string.SELECT REPLACE(‘OraclePLSQL’, ‘PL’, ‘SQL’) FROM dual;OracleSQLSQL
LPADPads the left side of a string with a specified set of characters.SELECT LPAD(‘Oracle’, 10, ‘*’) FROM dual;****Oracle
RPADPads the right side of a string with a specified set of characters.SELECT RPAD(‘Oracle’, 10, ‘*’) FROM dual;Oracle****
TRIMRemoves leading and trailing spaces (or specified characters) from a string.SELECT TRIM(‘ Oracle ‘) FROM dual;Oracle
LTRIMRemoves leading spaces (or specified characters) from a string.SELECT LTRIM(‘ Oracle’) FROM dual;Oracle
RTRIMRemoves trailing spaces (or specified characters) from a string.SELECT RTRIM(‘Oracle ‘) FROM dual;Oracle
SOUNDEXReturns a phonetic representation of a string.SELECT SOUNDEX(‘Oracle’) FROM dual;O624
ASCIIReturns the ASCII value of the first character in a string.SELECT ASCII(‘A’) FROM dual;65
CHRReturns the character corresponding to the ASCII value.SELECT CHR(65) FROM dual;A
TRANSLATEReplaces characters in a string based on corresponding characters in two other strings.SELECT TRANSLATE(‘SQL’, ‘SQ’, ‘PL’) FROM dual;PQL
REGEXP_LIKEDetermines if a string matches a regular expression pattern.SELECT REGEXP_LIKE(‘Oracle’, ‘Ora’) FROM dual;1 (true)
REGEXP_INSTRReturns the position of the first occurrence of a substring matching a regular expression.SELECT REGEXP_INSTR(‘OraclePLSQL’, ‘P.L’) FROM dual;7
REGEXP_SUBSTRReturns the substring matching a regular expression.SELECT REGEXP_SUBSTR(‘OraclePLSQL’, ‘P.L’) FROM dual;PL
REGEXP_REPLACEReplaces substrings matching a regular expression with another substring.SELECT REGEXP_REPLACE(‘OraclePLSQL’, ‘P.L’, ‘SQL’) FROM dual;OracleSQLSQL
INSTRReturns the position of the first occurrence of a substring.SELECT INSTR(‘OraclePLSQL’, ‘PL’) FROM dual;7
SUBSTRReturns a substring from the specified position.SELECT SUBSTR(‘OraclePLSQL’, 7, 5) FROM dual;PLSQL
TO_CHARConverts a number or date to a string.SELECT TO_CHAR(SYSDATE, ‘YYYY-MM-DD’) FROM dual;2024-06-04
TO_NUMBERConverts a string to a number.SELECT TO_NUMBER(‘12345’) FROM dual;12345
TO_DATEConverts a string to a date.SELECT TO_DATE(‘2024-06-04’, ‘YYYY-MM-DD’) FROM dual;04-Jun-24
REPLACEReplaces all occurrences of a substring within a string.SELECT REPLACE(‘OraclePLSQL’, ‘PL’, ‘SQL’) FROM dual;OracleSQLSQL
RPADPads the right side of a string with a specified set of characters.SELECT RPAD(‘Oracle’, 10, ‘*’) FROM dual;Oracle****
LPADPads the left side of a string with a specified set of characters.SELECT LPAD(‘Oracle’, 10, ‘*’) FROM dual;****Oracle
These functions provide various capabilities to manipulate and format string and character data in Oracle PL/SQL, allowing for complex transformations and queries involving text data.

Function NameDescriptionExample UsageResult
CONCATConcatenates two or more strings.SELECT CONCAT(‘Hive’, ‘QL’);HiveQL
CONCAT_WSConcatenates strings with a separator.SELECT CONCAT_WS(‘-‘, ‘Hive’, ‘QL’);Hive-QL
LENGTHReturns the length of a string.SELECT LENGTH(‘HiveQL’);6
LOWERConverts all characters in a string to lowercase.SELECT LOWER(‘HiveQL’);hiveql
UPPERConverts all characters in a string to uppercase.SELECT UPPER(‘HiveQL’);HIVEQL
REVERSEReverses the characters in a string.SELECT REVERSE(‘HiveQL’);LQeviH
TRANSLATEReplaces characters in a string.SELECT TRANSLATE(‘HiveQL’, ‘HQL’, ‘hql’);hiveql
TRIMRemoves leading and trailing spaces from a string.SELECT TRIM(‘ HiveQL ‘);HiveQL
LTRIMRemoves leading spaces from a string.SELECT LTRIM(‘ HiveQL’);HiveQL
RTRIMRemoves trailing spaces from a string.SELECT RTRIM(‘HiveQL ‘);HiveQL
SPACEReturns a string of spaces of the specified length.SELECT SPACE(3);‘ (3 spaces)
REPEATRepeats a string a specified number of times.SELECT REPEAT(‘Hive’, 3);HiveHiveHive
SPLITSplits a string into an array using a specified delimiter.SELECT SPLIT(‘Hive,QL’, ‘,’);[“Hive”, “QL”]
SUBSTRReturns a substring from the specified position.SELECT SUBSTR(‘HiveQL’, 1, 4);Hive
INSTRReturns the position of the first occurrence of a substring.SELECT INSTR(‘HiveQL’, ‘QL’);5
FIND_IN_SETReturns the position of a string in a comma-separated list.SELECT FIND_IN_SET(‘QL’, ‘Hive,QL,Hadoop’);2
INITCAPCapitalizes the first letter of each word in a string.SELECT INITCAP(‘hive ql’);Hive Ql
REGEXP_REPLACEReplaces substrings that match a regular expression.SELECT REGEXP_REPLACE(‘Hadoop Hive’, ‘H.*p’, ‘Map’);Map Hive
REGEXP_EXTRACTExtracts a substring that matches a regular expression.SELECT REGEXP_EXTRACT(‘Hadoop Hive’, ‘H\\w+’);Hadoop
FORMAT_NUMBERFormats a number to a string with the specified number of decimal places.SELECT FORMAT_NUMBER(12345.678, 2);12,345.68
RPADPads a string to the right with a specified character up to a certain length.SELECT RPAD(‘Hive’, 7, ‘#’);Hive###
LPADPads a string to the left with a specified character up to a certain length.SELECT LPAD(‘Hive’, 7, ‘#’);###Hive

Written by HintsToday Team

Related Posts

Get the latest news

Subscribe to our Newsletter

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *