Second Query Formatting Help Please explain the purpose of the formatting techni

Question

Second Query Formatting Help

Please explain the purpose of the formatting technique or the purpose of the function for each of the queries in the image below.

Worksheet Query Builder 13Query 5 14 SELECT INSTR('BROWNING, MARY','SUBSTR example" FROM DUAL; 15 16Query 6 17 SELECT SUBSTR ('BROWNING, MARY, INSTR ( BROWNING, MARY,+ "SUBSTR example" 18 FROM DUAL; 19 20Query 7 21 SELECT CONCAT 'SMITH',,' FROM DUAL 23Query 8 24 SELECT CONCAT (CONCAT ('SMITH,,', 'MARION') FROM DUAL; 25 26Query 9 27 SELECT LENGTH ('MARION SMITH') FROM DUAL: 28

Explanation / Answer

The Purpose of each of the above SQL functions are explained below:-

5. INSTR:- INSTR function returns the position of a substring in a string, and allows you to specify the start position and which occurrence to find.

Syntax:- INSTR(string, substring [, start [, occurrence]])

In this example the Original String is "BROWNING, MARRY", the substring is "," comma. So the first index where this comma appears in the original string will be returned as result as will be stored in Alias Column "SUBSTR example".

6. SUBSTR:- SUBSTR functions allows you to extract a substring from a string.

The syntax for the SUBSTR function in Oracle/PLSQL is:- SUBSTR( string, start_position [, length ] )

Parameters or Arguments:-

string - The source string.

start_position - The starting position for extraction. The first position in the string is always 1.

length - Optional. It is the number of characters to extract. If this parameter is omitted, the SUBSTR function will return the entire string.

eg:- SUBSTR('This is a test', 6, 2)

Result: 'is'

7. CONCAT:- CONCAT function allows you to concatenate strings together.

SYNTAX:- CONCAT( string1, string2, ... string_n )

Parameters or Arguments - string1, string2, ... string_n (The strings to concatenate together.)

SELECT CONCAT('SMITH',',') FROM DUAL;

Result is:- SMITH,

8. SELECT CONCAT(CONCAT('SMITH',', '),'MARION') FROM DUAL;

Output:- SMITH, MARION

9. LENGTH:- LENGTH function returns the length of the specified string.

Syntax:- LENGTH( string1 )

Parameters or Arguments:- string1 (The string to return the length for)

Returns - The LENGTH function returns a numeric value. If string1 is NULL, then the LENGTH function will return NULL.

SELECT LENGTH('MARION SMITH') FROM DUAL;

Result - 12

Please let me know in case of any clarifications required. Thanks!

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.