a.
“LENGTH” function:
- The “LENGTH()” function is used to return the length of a string that is return the number of characters in a given string.
- The syntax for “LENGTH()” function is
LENGTH(string)
- The parameter “string” implies the any string or any column name from specific table.
Example:
The example of “LENGTH()” function is given below:
CREATE TABLE STUDENT_NAMES(Student_ID integer PRIMARY KEY, Name text);
INSERT INTO STUDENT_NAMES VALUES(101,'John');
INSERT INTO STUDENT_NAMES VALUES(201,'Merry');
INSERT INTO STUDENT_NAMES VALUES(301,'Rose');
INSERT INTO STUDENT_NAMES VALUES(401,'Watson');
SELECT Name, LENGTH(Name) AS LengthOfName FROM STUDENT_NAMES;
Explanation:
The above query is used to display the length of each student name from “STUDENT_NAMES” table.
- User can create a table “STUDENT_NAMES” using “CREATE” and then insert the data into “STUDENT_NAMES” table using “INSERT” command.
- Using “SELECT” command, user can display the student name and its length.
- User can compute the length of each student name using “LENGTH” function.
- After that, the computed column is represented by “LengthOfName” with the help of “AS” operator.
When the query “SELECT Name, LENGTH(Name) AS LengthOfName FROM STUDENT_NAMES;” is executed, the following output will be displayed.
| Name | LengthOfName |
|--------|--------------|
| John | 4 |
| Merry | 5 |
| Rose | 4 |
| Watson | 6 |
“SUBSTR” function:
- The “SUBSTR()” function is used to extract a substring from a given string.
- The syntax for “SUBSTR()” function is
SUBSTR(sample_string, start_position, length)
- From the given syntax,
- The parameter “sample_string” implies the any string to extract from.
- The parameter “start_position” implies the starting position of given string.
- The parameter “length” implies number of characters to extract from the given string.
Example:
The example of “SUBSTR()” function is given below:
SELECT SUBSTR("Example SQL Query", 9, 3);
When the above query executed, the following output will be displayed.
Output: SQL
“INITCAP” function:
- The “INITCAP()” function is used to convert the first character of each word in the string to upper case character.
- The syntax for “INITCAP()” function is
INITCAP(sample_string)
- The parameter “sample_string” implies the any string.
Example:
The example of “INITCAP()” function is given below:
SELECT INITCAP('sample
Explanation:
- The above query is used to change the first letter of each word to upper case using “INITCAP” function.
- The computed column that is final result is represented by “Changed Word”.
- From the given query, the “DUAL” is a table which is automatically generated by Oracle
Database along with the data dictionary.- The “DUAL” table contains one column and one row. The column is defined to be “VARCHAR2(1)” and a row with a value “x”.
When the above query is executed, the following output will be displayed.
Output of the given Query:
| Changed String |
|--------------------|
| Sample Programming |
Three functions in Oracle, SQL Server and Access:
- “No”, the three functions are not same in SQL Server and access.
- Reasons for these condition.
- For Oracle:
- The functions “LENGTH”, “SUBSTR” and “INITCAP” are same for the Oracle.
- For SQL server:
- The function “LEN” is used to returns the length of the particular string.
- The “SUBSTRING” function is used to return a substring from a given string.
- The “INITCAP” function is not available in the SQL server.
- For Access:
- The function “LEN” is used to returns the length of the particular string.
- The “MID” function is used to return a substring from a given string.
- The “INITCAP” function is not available in Access.
- For Oracle:
Three functions in Oracle:
“LENGTH” function:
- The “LENGTH()” function is used to return the length of a string that is return the number of characters in a given string.
- The syntax for “LENGTH()” function is
LENGTH(string)
- The parameter “string” implies the any string or any column name from specific table.
Example:
The example of “LENGTH()” function is given below:
SELECT LENGTH('SQL Programming Concept') "Length of Characters" FROM DUAL;
Explanation:
- The above query is used to display the length of character from given string “'SQL Programming Concept” using “LENGTH” function.
- The computed column that is final result is represented by “Length of Characters”.
- From the given query, the “DUAL” is a table which is automatically generated by Oracle Database along with the data dictionary.
- The “DUAL” table contains one column and one row. The column is defined to be “VARCHAR2(1)” and a row with a value “x”.
When the above query is executed, the following output will be displayed.
Output of the given Query:
| Length of Characters |
|----------------------|
| 23 |
“SUBSTR” function:
- The “SUBSTR()” function is used to extract a substring from a given string.
- The syntax for “SUBSTR()” function is
SUBSTR(sample_string, start_position, length)
- From the given syntax,
- The parameter “sample_string” implies the any string to extract from.
- The parameter “start_position” implies the starting position of given string.
- The parameter “length” implies number of characters to extract from the given string.
Example:
The example of “SUBSTR()” function is given below:
SELECT SUBSTR('Example SQLQuery Result', 9, 8) "SubString" FROM DUAL;
Explanation:
- The above query is used to display the substring from the given string “'Example SQLQuery Result” using “SUBSTR” function.
- The computed column that is final result is represented by “SubString”.
- From the given query, the “DUAL” is a table which is automatically generated by Oracle Database along with the data dictionary.
- The “DUAL” table contains one column and one row. The column is defined to be “VARCHAR2(1)” and a row with a value “x”.
When the above query executed, the following output will be displayed.
Output of the given Query:
| SubString |
|-----------|
| SQLQuery |
“INITCAP” function:
- The “INITCAP()” function is used to convert the first character of each word in the string to upper case character.
- The syntax for “INITCAP()” function is
INITCAP(sample_string)
- The parameter “sample_string” implies the any string.
Example:
The example of “INITCAP()” function is given below:
SELECT INITCAP('sample programming') "Changed Word" FROM DUAL;
Explanation:
- The above query is used to change the first letter of each word to upper case using “INITCAP” function.
- The computed column that is final result is represented by “Changed Word”.
- From the given query, the “DUAL” is a table which is automatically generated by Oracle Database along with the data dictionary.
- The “DUAL” table contains one column and one row. The column is defined to be “VARCHAR2(1)” and a row with a value “x”.
When the above query is executed, the following output will be displayed.
Output of the given Query:
| Changed String |
|--------------------|
| Sample Programming |
Three functions in SQL Server:
“LEN” function:
- The “LEN()” function is used to return the length of a string that is return the number of characters in a given string.
- The syntax for “LEN()” function is
LEN(string)
- The parameter “string” implies the any string.
Example:
The example of “LEN()” function is given below:
SELECT LEN('SQL Programming Concept');
Explanation:
- The above query is used to display the length of character from given string “'SQL Programming Concept” using “LEN” function.
When the above query is executed, the following output will be displayed.
Output of the given Query:
23
“SUBSTRING” function:
- The “SUBSTRING()” function is used to extract a substring from a given string.
- The syntax for “SUBSTRING()” function is
SUBSTRING(sample_string, start_position, length)
- From the given syntax,
- The parameter “sample_string” implies the any string to extract from.
- The parameter “start_position” implies the starting position of given string.
- The parameter “length” implies number of characters to extract from the given string.
Example:
The example of “SUBSTR()” function is given below:
SELECT SUBSTRING('Example SQLQuery Result', 9, 8) AS SubString;
Explanation:
- The above query is used to display the substring from the given string “'Example SQLQuery Result” using “SUBSTRING” function.
- The computed column that is final result is represented by “SubString” with the help of “AS” operator.
When the above query executed, the following output will be displayed.
Output of the given Query:
SubString
SQLQuery
“INITCAP” function:
This function is not available in SQL server.
Three functions in Access:
“LEN” function:
- The “LEN()” function is used to return the length of a string that is return the number of characters in a given string.
- The syntax for “LEN()” function is
LEN(string/variable name)
- The parameter “string” implies the any string or any column name from specific table.
Example:
The example of “LEN()” function is given below:
SELECT LEN('SQL Programming Concept') AS Length_Of_String;
Explanation:
- The above query is used to display the length of character from given string “'SQL Programming Concept” using “LEN” function.
- The computed column that is final result is represented by “Length_Of_String” with the help of “AS” operator.
When the above query is executed, the following output will be displayed.
Output of the given Query:
Length_Of_String
23
“MID” function:
- The “MID()” function is used to extract a substring from a given string.
- The syntax for “MID()” function is
MID(sample_string, start_position, length)
- From the given syntax,
- The parameter “sample_string” implies the any string to extract from.
- The parameter “start_position” implies the starting position of given string.
- The parameter “length” implies number of characters to extract from the given string.
Example:
The example of “MID()” function is given below:
CREATE TABLE STUDENT_NAMES(Student_ID integer PRIMARY KEY, Name text);
INSERT INTO STUDENT_NAMES VALUES(301,'John Merry');
INSERT INTO STUDENT_NAMES VALUES(401,'Rose Lilly');
SELECT Name, MID(STUDENT_NAMES, 6, 5) AS SubString_Names FROM STUDENT_NAMES;
Explanation:
The above query is used to display the sub string each student name from “STUDENT_NAMES” table.
- User can create a table “STUDENT_NAMES” using “CREATE” and then insert the data into “STUDENT_NAMES” table using “INSERT” command.
- Using “SELECT” command, user can display the student name and its length.
- User can compute the sub string of each student name using “MID” function.
- After that, the computed column is represented by “SubString_Names” with the help of “AS” operator.
When the above query executed, the following output will be displayed.
Output of the given Query:
SubString_Names
Merry
Lilly
“INITCAP” function:
This function is not available in access.
Explanation of Solution
Oracle SQL query to display characters from the “TYPE” column in the “TRIP” table:
The Oracle SQL query to display three characters on the left from the “TYPE” column of the “TRIP” table is given below:
SELECT SUBSTR(TYPE,0,3) "SubString from TYPE Column" FROM TRIP;
Explanation:
The above query is used to display three characters on the left from the “TYPE” column of the “TRIP” table.
- Using “SUBSTR” function, user can display the three characters on the left from the “TYPE” column with three arguments “TYPE” column, start position “0” and number of character to extract “3”.
- The computed column is represented by “SubString from TYPE Column”.
- When the above query is executed, the following output will be displayed.
Screenshot of output:
Explanation of Solution
Oracle SQL query to display characters from the “START_LOCATION” column of the “TRIP” table:
The Oracle and SQL query for starting at the fourth character from the left, display the next five characters in the “START_LOCATION” column of the “TRIP” table is given below:
SELECT SUBSTR(START_LOCATION,4,5) "SubString of START_LOCATION" FROM TRIP;
Explanation:
The above query is used to display the given characters from “START_LOCATION” column of the “TRIP” table.
- Using “SUBSTR” function, user can display the given characters from the “START_LOCATION” column with three arguments “START_LOCATION” column, start position “3” and number of character to extract “5”.
- The computed column is represented by “SubString of START_LOCATION”.
- When the above query is executed, the following output will be displayed.
Screenshot of output:
Want to see more full solutions like this?
Chapter 8 Solutions
A Guide to SQL
- Write the SQL code to delete the row for William Smithfield, who was hired on June 22, 2004, and whose job code is 500. (Hint: Use logical operators to include all of the information given in this problem.)arrow_forwardWrite the SQL code that will change the PROJ_NUM to 14 for employees who were hired before January 1, 1994, and whose job code is at least 501. (You may assume that the table will be restored to its condition preceding this question.)arrow_forwardSQL includes many numerical functions. Two of these functions are FLOOR and CEIL. Use the Internet to research these functions. Are the functions available in Oracle, SQLServer, and Access? Write a paragraph that discusses what the functions do and anydifferences and/or similarities between the functions in Oracle, SQL Server, and Access.Then perform the following tasks: a. Solmaris Condominium Group would like to know the impact of discounting its condofees by 3 percent. Write an SQL statement in Oracle that displays the condo ID, unitnumber, discounted condo fee, discounted condo fee with the CEIL function, anddiscounted condo fee with the FLOOR function. b. Based on your research, will the values in the three columns vary? If so, how? Usethe condo with the ID of 1 to explain your answer. Be sure to cite your references.arrow_forward
- A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology PtrDatabase Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher:Cengage LearningDatabase Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781285196145Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos CoronelPublisher:Cengage Learning