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

undefined
More Posts by HintsToday

Related Posts

Temporary Functions in PL/Sql Vs Spark Sql

Jun 26, 2024 |

Temporary functions allow users to define functions that are session-specific and used to encapsulate reusable logic within a database session. While both PL/SQL and Spark SQL support the concept of user-defined functions, their implementation and usage differ...

read more

Spark SQL windows Function and Best Usecases

Jun 26, 2024 |

For Better understanding on Spark SQL windows Function and Best Usecases do refer our post Window functions in Oracle Pl/Sql and Hive explained and compared with examples. Window functions in Spark SQL are powerful tools that allow you to perform calculations across a...

read more

Spark SQL Join Types- Syntax examples, Comparision

Jun 26, 2024 |

Spark SQL supports several types of joins, each suited to different use cases. Below is a detailed explanation of each join type, including syntax examples and comparisons. Types of Joins in Spark SQL Inner Join Left (Outer) Join Right (Outer) Join Full (Outer) Join...

read more

Indexing in SQL- Explain with examples

Apr 8, 2024 |

Indexing in SQL is a technique used to improve the performance of queries by creating special data structures (indexes) that allow for faster data retrieval. Indexes are created on one or more columns of a table, and they store the values of those columns in a sorted...

read more

Pattern matching in SQL- Like Operator

Apr 7, 2024 |

LIKE Operator: The LIKE operator is used to search for a specified pattern in a column. It allows the use of wildcards: % (percent sign): Matches zero or more characters. _ (underscore): Matches any single character. Examples: SELECT * FROM employees WHERE last_name...

read more

Order of appearance and execution in the SELECT query?

Apr 7, 2024 |

For Appearance( So Few Jawans & Workers Go Home On Late) SELECT – FROM – JOIN – ON – WHERE – GROUP BY – HAVING – ORDER BY – LIMIT FROM – JOIN – ON – WHERE – GROUP BY – HAVING – SELECT – ORDER...

read more

Functions in SQL- Examples

Apr 7, 2024 |

SQL provides various functions to perform operations on data stored in a database. Here are some commonly used SQL functions categorized based on their functionality: Aggregate Functions: COUNT(): Counts the number of rows. SUM(): Calculates the sum of values. AVG():...

read more

0 Comments

Submit a Comment

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