How to Use LENGTH Function to Find String Length in SQL?
The LENGTH function in SQL returns the length of the word or the number of characters present in the string. The LENGTH function is a type of string function. We can find the LENGTH function in every relational database like SQL Server, MySQL, PostgreSQL, etc. We use LEN instead of LENGTH in a few databases, as in SQL Server.
Syntax of LENGTH Function
The following illustrates the syntax of the LENGTH function.
Parameters of LENGTH in SQL
The LENGTH function only accepts one parameter, i.e. string whose length is to be determined. It takes strings in the following formats:
- Raw string
- A string variable
- An expression returning string
- Float or Numeric values
Return Value of LENGTH in SQL
The LENGTH function returns the length of the string in bytes. If the input string is empty, the LENGTH returns 0. It returns NULL if the input string is NULL.
Learn More about Advanced Concepts of SQL
Examples of LENGTH Function in SQL
Example 1
In this example, we will find the string "Scaler" length.
Output:
In this example, we have found the length of the word "Scaler". The output is 6.
Example 2
In this example, we will take the length of the floating value from the demo table.
Table Demo
Taxno | name | taxrate |
---|---|---|
1 | Ryan | 6.55 |
2 | Joanna | 8.243 |
3 | Jennie | 5.2455 |
4 | Max | 7.43 |
5 | Siya | 6.5 |
Output:
Taxno | name | taxrate | len_taxrate |
---|---|---|---|
1 | Ryan | 6.55 | 4 |
2 | Joanna | 8.22 | 5 |
3 | Jennie | 5.2455 | 6 |
4 | Max | 7.43 | 4 |
5 | Siya | 6.5 | 3 |
In the above example, the LENGTH function returns the length of the float value.
Example 3
In this example, we will find the length of the item_name from the below table:
Table Items
item_no | item_name | Price |
---|---|---|
1 | Biscuit | 50 |
2 | Choclates | 100 |
3 | Pasta | 120 |
4 | Mayonnaise | 90 |
5 | Ketchup | 80 |
Output:
item_no | item_name | Price | len_of_item |
---|---|---|---|
1 | Biscuit | 50 | 7 |
2 | Choclates | 100 | 9 |
3 | Pasta | 120 | 5 |
4 | Mayonnaise | 90 | 10 |
5 | Ketchup | 80 | 7 |
In this way, we can use a LENGTH function to calculate the length of any given string.
Conclusion
- The length function calculates the length of a string.
- It returns the length of the given string in bytes.
- The LENGTH function returns a NULL value if the input string is NULL.
- The LENGTH function is used in SELECT statements and data manipulation statements.