EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials Oracle Tutorial Oracle LENGTH()
Secondary Sidebar
Oracle Tutorial
  • Advanced
    • Timestamp to Date in Oracle
    • Oracle Golden Gate
    • Oracle Virtual Machine
    • Oracle Describe Table
    • Oracle Clauses
    • Oracle Having Clause
    • Oracle?Primavera
    • Oracle FOREIGN Key
    • PIVOT in Oracle
    • Oracle Alter Table
    • Oracle Queries
    • Oracle Views
    • Oracle Window Functions
    • Oracle String Functions
    • Oracle Date Functions
    • Oracle Analytic Functions
    • Oracle Aggregate Functions
    • Select in Oracle
    • INSERT in Oracle
    • DISTINCT in Oracle
    • Function in Oracle
    • Oracle GROUP_CONCAT
    • Oracle INSTR()
    • Oracle CONVERT
    • Oracle LENGTH()
    • Oracle EXISTS
    • Oracle REPLACE()
    • Oracle MERGE
    • Oracle LEAD()
    • Oracle EXTRACT()
    • Oracle LISTAGG()
    • Oracle SYS_CONTEXT()
    • Oracle COALESCE
    • Oracle NVL()
    • Oracle SYSDATE()
    • Oracle?Date Format
    • Oracle SYS_GUID()
    • Oracle WILDCARDS
    • Oracle Synonyms
    • Oracle Subquery
    • BETWEEN in Oracle
    • FETCH in Oracle
    • Oracle Index
    • Oracle Function-based Index
    • Oracle UNIQUE Index
    • Oracle Bitmap Index
    • Oracle Column
    • Oracle Triggers
    • Oracle Procedures
    • Sample Database for Oracle
    • Oracle LIKE Operator
    • ORDER BY in Oracle
    • Oracle ORDER BY DESC
    • GROUP BY in Oracle
    • Oracle GROUP BY HAVING
    • Oracle Aliases
    • Table in Oracle
    • Oracle Temporary Table
    • Oracle? Table Partition
    • Oracle rename table
    • Oracle CTE
    • Cursor in Oracle
    • Oracle LOCK TABLE
    • Oracle Tablespace
    • Oracle CARDINALITY
    • Oracle REGEXP
    • Oracle REGEXP_REPLACE
    • Oracle to_date
    • JSON in Oracle
    • Oracle COMMIT
    • Oracle GRANT
    • Oracle MD5
    • Oracle ROLLBACK
    • Oracle Users
    • Oracle TIMESTAMP
    • IF THEN ELSE in Oracle
    • Oracle While Loop
    • Oracle Clone Database
    • Oracle Backup Database
    • Oracle? XML
    • Oracle XMLAGG
    • Oracle XMLTABLE
    • Oracle Performance Tuning
    • Oracle B Tree Index
    • Oracle fusion
    • Oracle ebs
    • Oracle GRC
    • Oracle ERP
    • Oracle ASM
    • Oracle Cloud
    • Oracle HCM Cloud
    • Oracle Integration Cloud
    • Oracle Jinitiator
    • Oracle pathfinder
    • Oracle VirtualBox
    • Oracle Weblogic Server
    • Oracle decode
    • Oracle Exadata
    • Oracle ZFS
    • Oracle? utilities
    • JDBC Driver for Oracle
    • Oracle? DBA Versions
    • Oracle DBA Salary
  • Basic
    • Oracle Marketing Cloud
    • What is Oracle?
    • Career in Oracle
    • How to Install Oracle
    • Oracle Versions
    • What Is Oracle Database
    • Oracle Data Warehousing
    • Oracle Warehouse Builder
    • Career In Oracle Database Administrator
    • Career In Oracle DBA
    • What is Oracle RAC
    • Oracle DBA
    • Oracle? Vanderbilt
    • What is RMAN Oracle
    • Oracle Database Administration
    • Oracle Operators
    • Oracle Constraints
    • Oracle number
    • Oracle Data Types
    • Oracle UNIQUE Constraint
    • Oracle Check Constraint
  • Joins
    • Joins in Oracle
    • Inner Join in Oracle
    • Oracle Cross Join
    • Left Join in Oracle
    • OUTER Join in Oracle
    • Oracle Full Outer Join
    • Natural Join in Oracle
    • Oracle Self Join
    • Oracle hash join
    • Oracle? Update with Join
  • Oracle SET Operators
    • UNION in Oracle
    • Oracle UNION ALL
    • INTERSECT in Oracle
    • MINUS in Oracle
  • Interview Questions
    • Oracle Interview Questions
    • Oracle Apps Interview Questions
    • Oracle Apps Technical Interview Questions
    • Oracle Database Interview Questions
    • Oracle Forms Interview Questions
    • Oracle PL/SQL Interview Questions
    • Oracle RAC Interview Questions
    • Oracle SOA Interview Questions

Related Courses

Oracle Course Training

Oracle DBA Certification Course

MongoDB Certification Training

Oracle LENGTH()

By Priya PedamkarPriya Pedamkar

Oracle LENGTH()

Introduction to Oracle LENGTH()

Oracle LENGTH( ) function can be defined as a function which is used to calculate the length of a string and it measures the length of the string in characters (A single string is made of many characters and the data type of the string expression can be VARCHAR2, CHAR, NCHAR, CLOB or NCLOB) as defined by the input character set and it returns a positive value upon execution which is a integer representing the number of characters present in the string.

Syntax

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Let us now discuss about the syntax of the LENGTH( ) function. The syntax is very simple.

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (86,112 ratings)

LENGTH(string_expression)

Parameter

string_expression: This refers to the string whose length we are going to find. The string_expression parameter can be a variable, constant or a column of a table.

How LENGTH Function Works In Oracle

The length function has discussed earlier is used to return the length of the string which we pass as a parameter. It accepts only one argument or parameter. The function when executed calculates the total number of characters present in the expression and then returns a positive integer which equals the number of characters. The point to keep in mind that in case the string_expression is NULL then the length function returns NULL. Another important case is that if the string_expression is of CHAR datatype then it will calculate both trailing as well as leading blanks.

Examples to Implement LENGTH( ) Function in Oracle

Let us look at the examples for ORACLE LENGTH( ) function for getting a better understanding of different cases where we can use the function.

Example #1

LENGTH OF A STRING: In this case we will pass a string constant inside the parenthesis of the length function. The length function will be used to find the length of the string. Let us look at the query.

Code:

SELECT LENGTH('Ram is a good boy')"LENGTH OF THE STRING" from DUAL;

Output:

Length of a String

Explanation: In this query the string expression used is: Ram is a good boy. Let us execute the query in SQL developer and check the output of the query.As we can see in the above screen shot the output shows the length of the string.

Example #2

LENGTH FUNCTION WHERE THE STRING EXPRESSION IS NULL: In the second case we will check how the length function responds when the string expression passed inside the parenthesis of the function is NULL. Let us look at the query for the same.

Code:

SELECT LENGTH('')"LENGTH OF THE STRING" from DUAL;

Output:

Length of function

Explanation: As we can see in the query that the expression passed is NULL. Let us now execute the same query in SQL DEVELOPER. As we can see in the above screen shot that upon execution the query returns the length as NULL.

Example #3

LENGTH FUNCTION ON COLUMN OF A TABLE: As we had discussed earlier in this article that the length function can also be applied to columns of a table.

Consider Following Employee Table:

select * from Employee;

Column of a table

In this example we will try to get the length of each employee PROFILE present in the PROFILE column of the EMPLOYEE table in oracle database and we will order by descending order. Let us look at the query for the same.

Code:

SELECT
PROFILE,
LENGTH(PROFILE) "LENGTH OF THE PROFILE"
FROM
Employee
ORDER BY
LENGTH(PROFILE) DESC;

Output:

Oracle LENGTH()4

Explanation: As we can see in the above query we have used the column PROFILE to find the length of each employee PROFILE present in the column PROFILE.As we can see that the output consists of a separate column displaying the length of every employee name present in the name column.

Example #4

LENGTH FUNCTION USING GROUP BY CLAUSE: In this case we will discuss the use of length function using GROUP BY clause. In this example we will group the employees present in the employee table by the length of their PROFILE and also displays the count of employee PROFILE we have in each group. Let us now look at the query for the same.

Code:

SELECT
LENGTH(PROFILE) "LENGTH OF THE PROFILE",
COUNT(*)
FROM
Employee
GROUP BY
LENGTH(PROFILE);

Output:

Group by clause

Explanation: As we can see in the above query that we have used length function with the group by clause and count function to count the number of employees present in each group. As we can see in the output screen shot that the query groups the employee table by the length of the employee PROFILE and also displays the count of employee PROFILE in each group.

Example #5

LENGTH FUNCTION WITH CASE: We will now check the use of length function on condition statements like case and along with it we will also use concat function. In this example we will only add three dots with the PROFILE whose length are more than FIVE characters and rest PROFILES will be displayed as usual. To achieve this we will use CASE statement for the condition and we will concat function to add the three dots to only those PROFILE whose length will be more than five characters. Let us now look at the query for the same.

Code:

SELECT
PROFILE,
CASE
WHEN LENGTH( PROFILE ) > 5 THEN CONCAT( PROFILE, '...' )
ELSE PROFILE
END NEW_PROFILE
FROM
Employee
ORDER BY
PROFILE desc;

Output:

Length fuction with case

Explanation: As we can see in the query that the alias NEW_PROFILE represents the CASE block and the PROFILE column represents the actual original PROFILES of the employees. As we can see in the screen shot that two names have three dots attached to them as they satisfy the condition.

Conclusion

In this article we discussed about the definition of the length function in oracle along with the syntax. Later in the article we discussed about the various examples representing various condition on which the length function can be used.

Recommended Articles

This is a guide to Oracle LENGTH(). Here we discuss an introduction to Oracle LENGTH(), syntax, how does it work, examples with code and output. You can also go through our other related articles to learn more –

  1. Oracle Subquery
  2. Function in Oracle
  3. Natural Join in Oracle
  4. Oracle Window Functions
  5. Oracle While Loop | How to Works?
Popular Course in this category
Oracle Training (14 Courses, 8+ Projects)
  14 Online Courses |  8 Hands-on Projects |  120+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

Oracle DBA Database Management System Training (2 Courses)4.9
All in One Financial Analyst Bundle- 250+ Courses, 40+ Projects4.8
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more