Chapter 8, Problem 9CAT

Program Plan Intro

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 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 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.

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.

Sample Output

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.

Sample Output

Screenshot of output: