Returning a Location of a Substring
Retrieving Data with Queries
Querying and Manipulating Data 2-13
23 rows selected
The
REGEXPR_INSTR
function enables you to find the position of the first substring
, you use
REGEXPR_INSTR
to find a space
character, '
'. Note that the metacharacter '+' indicates multiple occurrences of the
pattern. This result set shows the position of the first space in each address. See Oracle
Database SQL Language Reference for information about the
REGEXP_INSTR
expression.
Example 214 Returning a Location of a Substring
SELECT street_address, REGEXP_INSTR(street_address, '[ ]+', 1, 1) "Position"
FROM locations;
The results of the query appear.
STREET_ADDRESS Position
---------------------------------------- ----------------------
1297 Via Cola di Rie 5
93091 Calle della Testa 6
2017 Shinjuku-ku 5
...
23 rows selected
The function
REGEXPR_COUNT
determines the number of times the specified character
,
REGEXPR_COUNT
returns the number of
times the space character occurs in the
street_address
column of the table
locations
. See Oracle Database SQL Language Reference for information about the
REGEXP_COUNT
expression.
Example 215 Returning the Number of Occurrences of a Substring
SELECT street_address, REGEXP_COUNT(street_address, ' ', 1) "Number of Spaces"
FROM locations;
The results of the query appear.
STREET_ADDRESS Number of Spaces
---------------------------------------- ----------------------
1297 Via Cola di Rie 4
93091 Calle della Testa 3
2017 Shinjuku-ku 1
...
23 rows selected
This result set shows the number of spaces in each address.
Sorting Data
In SQL, the ORDER BY clause is used to identify which columns are used to sort the
resulting data. The sort criteria does not have to be included in the result set, and can
include expressions, column names, arithmetic operations, user-defined functions, and
so on.
ORDER BY
clause that returns the result set sorted in order of
last_name
, in ascending order.
See Also:
Oracle Database SQL Language Reference for syntax of regular
expressions
