Manipulating Strings: SUBSTR and concatenation : SUBSTR « Character String Functions « Oracle PL/SQL Tutorial

SQL>
SQL> CREATE TABLE emp (
  2     empID INT NOT NULL PRIMARY KEY,
  3     Name      VARCHAR(50) NOT NULL);

Table created.

SQL> INSERT INTO emp (empID,Name) VALUES (1,'Tom');

1 row created.

SQL> INSERT INTO emp (empID,Name) VALUES (2,'Jack');

1 row created.

SQL> INSERT INTO emp (empID,Name) VALUES (3,'Mary');

1 row created.

SQL> INSERT INTO emp (empID,Name) VALUES (4,'Bill');

1 row created.

SQL> INSERT INTO emp (empID,Name) VALUES (5,'Cat');

1 row created.

SQL> INSERT INTO emp (empID,Name) VALUES (6,'Victor');

1 row created.

SQL>
SQL>
SQL>
SQL> SELECT
  2     SUBSTR(Name, INSTR(Name, ' ') + 1) || ', ' ||
  3     SUBSTR(Name, 1, INSTR(Name, ' ') - 1) AS empName
  4  FROM emp
  5  ORDER BY empName;

EMPNAME
--------------------------------------------------------------------------------
Bill,
Cat,
Jack,
Mary,
Tom,
Victor,

6 rows selected.

SQL>
SQL>
SQL> drop table emp;

Table dropped.

11.18.SUBSTR
	11.18.1.	The SUBSTR Function
	11.18.2.	substr(,) and substr(,,)
	11.18.3.	SUBSTR(x, start [, length]) returns a substring of x that begins at the position specified by start.
	11.18.4.	Substr retrieves a portion of the string
	11.18.5.	Retrieval begins at position 5 and again goes for 12 characters
	11.18.6.	A retrieval with no third argument: it starts at begin and retrieves the rest of the string
	11.18.7.	If begin is negative, then retrieval occurs from the right-hand side of original string
	11.18.8.	SUBSTR and INSTR used together
	11.18.9.	Combine SUBSTR and INSTR to deal with a table column
	11.18.10.	Combine DECODE and SUBSTR together
	11.18.11.	Example SELECT output using SUBSTR().
	11.18.12.	substr birthday
	11.18.13.	Compare substring in where statement
	11.18.14.	Manipulating Strings: SUBSTR and concatenation
	11.18.15.	substr(date value,8)+16