To report the position of the first space in each STREET_ADDRESS, you can use the REGEXP_INSTR function, as in Example 4-13.Įxample 4-16 Selecting Data from Two Tables (Joining Two Tables) SELECT EMPLOYEES.FIRST_NAME "First", Magdalen Centre, The Oxford Science Park 5 Result is similar to: STREET_ADDRESS Number of Spaces REGEXP_COUNT(STREET_ADDRESS, ' ') "Number of Spaces" To count the number of spaces in each STREET_ADDRESS, you can use the REGEXP_COUNT function, as in Example 4-12.Įxample 4-12 Counting the Number of Spaces in Each STREET_ADDRESS SELECT STREET_ADDRESS, REGEXP_SUBSTR(STREET_ADDRESS, '-]+') "Number" (In the replace string, \ is not an escape character.)Įxample 4-11 Extracting the Street Number from Each STREET_ADDRESS COLUMN Number FORMAT A8
The replace string uses \1, \2, and \3 to represent the first, second, and third regular expressions in the search pattern, respectively. Without the escape character, the metacharacter. The character immediately after an escape character is interpreted as a literal. The metacharacter ] represents a digit, the metacharacter specifies n occurrences, and the metacharacter \ is an escape character. The search pattern has three regular expressions, each of which is enclosed in parentheses. You can use the REGEXP_REPLACE function, with regular expressions in the search pattern (the stored format) and references to those regular expressions in the replace string (the display format), as in Example 4-10. nnnn with their equivalents in the format ( nnn ) nnn-nnnn. Suppose that, in the displayed query results, you want to replace phone numbers that are stored in the format nnn. Result is similar to: FIRST_NAME LAST_NAME The third function parameter, 'i', specifies that the match is case-insensitive.Įxample 4-9 Selecting All Employees Whose Last Names Have Double Vowels SELECT FIRST_NAME, LAST_NAME The metacharacter \1 represents the first (and in this case, only) regular expression. The regular expression () represents any vowel. Suppose that you want to select every employee whose last name has a double vowel (two adjacent occurrences of the same vowel).
Result is similar to: FIRST_NAME LAST_NAME JOB_ID The Results pane appears, showing the results of the query, which are similar to: FIRST_NAME LAST_NAME DEPARTMENT_IDĮxample 4-8 Selecting All Managers in the EMPLOYEES Table SELECT FIRST_NAME, LAST_NAME, JOB_ID In the field under "Enter SQL Statement:", enter this query: SELECT FIRST_NAME, LAST_NAME, DEPARTMENT_ID To select only FIRST_NAME, LAST_NAME, and DEPARTMENT_ID:
#ORACLE SQL DEVELOPER NVL HOW TO#
This tutorial shows how to select only the columns FIRST_NAME, LAST_NAME, and DEPARTMENT_ID of the EMPLOYEES table. Tutorial: Selecting Specific Columns of a Table The query and its results disappear, and you can enter another SQL statement in the SQL Worksheet. The Results pane appears, showing the result of the query. In the SQL Worksheet pane, type a query (a SELECT statement). Under "Enter SQL Statement:" is a field where you can enter a SQL statement. If the Connection field does not have the value hr_conn, select that value from the drop-down list. This topic explains how to run queries or other SQL statements in SQL Developer, if you are connected to Oracle Database as user HR from SQL Developer (for instructions, see "Connecting to Oracle Database as User HR from SQL Developer").Įither the Connection Information window opens or the SQL Worksheet pane appears.
0 kommentar(er)
