uko ugs ql master class

Upload: bhuvnesh-pandey

Post on 14-Apr-2018

226 views

Category:

Documents


0 download

TRANSCRIPT

  • 7/27/2019 Uko Ugs Ql Master Class

    1/78

    Advanced SQL

    UKOUG ConferenceMasterclass

    Daniel FinkOptimalDBA

  • 7/27/2019 Uko Ugs Ql Master Class

    2/78

    Learning FeaturesDont try to create the final query thefirst time

    Use the Lego approach

    Start with raw data and then add in theadditional processingAlways double check results

    Eyeball usually worksMay need a calculator

    Begin to rethink how you construct aquery

  • 7/27/2019 Uko Ugs Ql Master Class

    3/78

    LogisticsPresentation format

    Slides for conceptSQL*Plus script/output

    Use personal DEMO schemaScript and output

    Ask Questions during topic

  • 7/27/2019 Uko Ugs Ql Master Class

    4/78

    AgendaAnalytical FunctionsRegular Expressions

    I will not cover 11g

  • 7/27/2019 Uko Ugs Ql Master Class

    5/78

    Analytical FunctionsDocumented in Oracle Data Warehousing

    Guide and SQL GuideCore ConceptsProcessingPartitionWindow

    Analytical Function TypesRanking

    Other ValueWindow AggregationStatistical *wont cover these

  • 7/27/2019 Uko Ugs Ql Master Class

    6/78

    Uses of Analytical SQLTop/Bottom N queries

    Top 5 sales peopleBottom 15 customers

    ComparisonsMonth to month sales increases/decreasesRanking/Ratios

    Top 10 selling items% of overall sales by item

  • 7/27/2019 Uko Ugs Ql Master Class

    7/78

    Why Analytical SQLImproved code

    ReadabilitySupportability

    Improved PerformanceFewer passes on a tableLess resource consumptionFaster run time

    Part of the core product

  • 7/27/2019 Uko Ugs Ql Master Class

    8/78

    SQL*Plus DemoReport on all employees sorted by salaryand show their numeric ranking

  • 7/27/2019 Uko Ugs Ql Master Class

    9/78

    SELECT e.name,e.salary,DENSE_RANK() OVER

    (ORDER BY salary DESC) AS sal_rank

    FROM employee eORDER BY e.salary DESC

    Analytical Function

    ORDER BY clause

  • 7/27/2019 Uko Ugs Ql Master Class

    10/78

    PartitioningNot to be confused with table/index partitioning

    A set of rows grouped by a defined dataelementDefault partition is entire result set

    Fixed by data valuesFunctions are applied within the partition

    Values are reset at partition boundaries

    1 partition per functionMultiple partitions per statement

  • 7/27/2019 Uko Ugs Ql Master Class

    11/78

    SQL*Plus DemoReport on all employees sorted bydepartment number and salary and showtheir numeric ranking within theirdepartment

  • 7/27/2019 Uko Ugs Ql Master Class

    12/78

  • 7/27/2019 Uko Ugs Ql Master Class

    13/78

    SELECT

    WHERE/joins

    GROUP BY/HAVING

    Partitioning

    Ordering

    Windowing

    ApplyFunctions

    ORDER BY

    SELECT e.name,d.name dept_name,e.salary

    FROM employee e,department d

    WHERE d.dept_id = e.dept_id

    DENSE_RANK() OVER

    (PARTITION BY d.nameORDER BY e.salary DESC) AS sal_rank

    ORDER BY d.dept_id,e.salary DESC

  • 7/27/2019 Uko Ugs Ql Master Class

    14/78

    Employee Department Salary-------------------- -------------------- ---------

    Allison Ballinger Administration $90,000Julie Johnson Sales and Marketing $75,000Stan Marsh Accounting $45,000Larry Wilton Sales and Marketing $30,000Thomas Walton Logistics and Supply $25,000Tina Walton Logistics and Supply $80,000Shelly Walton Sales and Marketing $30,000Eric Kraus Logistics and Supply $27,000John Dennis Accounting $46,000Bobby Harris Sales and Marketing $30,000Doug Harris Logistics and Supply $50,000Erika Deeter Accounting $60,000

    William Dietrich Administration $30,000 Allison Dietrich Sales and Marketing $30,000 Andy Schmidt Sales and Marketing $70,000

    Rachel Middleton Administration $850,000Henry Parry Sales and Marketing $30,000Bev George Sales and Marketing $30,000Oscar Perry Sales and Marketing $45,000Billy Yolto Logistics and Supply $30,000

    Vincent Johns Logistics and Supply $50,000

  • 7/27/2019 Uko Ugs Ql Master Class

    15/78

    Employee Department Salary-------------------- -------------------- ---------

    Allison Ballinger Administration $90,000 William Dietrich Administration $30,000

    Rachel Middleton Administration $850,000Julie Johnson Sales and Marketing $75,000Larry Wilton Sales and Marketing $30,000Shelly Walton Sales and Marketing $30,000Bobby Harris Sales and Marketing $30,000

    Allison Dietrich Sales and Marketing $30,000 Andy Schmidt Sales and Marketing $70,000

    Henry Parry Sales and Marketing $30,000Bev George Sales and Marketing $30,000Oscar Perry Sales and Marketing $45,000Stan Marsh Accounting $45,000John Dennis Accounting $46,000Erika Deeter Accounting $60,000Thomas Walton Logistics and Supply $25,000Tina Walton Logistics and Supply $80,000Eric Kraus Logistics and Supply $27,000Doug Harris Logistics and Supply $50,000Billy Yolto Logistics and Supply $30,000

    Vincent Johns Logistics and Supply $50,000

  • 7/27/2019 Uko Ugs Ql Master Class

    16/78

    Employee Department Salary-------------------- -------------------- ---------Rachel Middleton Administration $850,000

    Allison Ballinger Administration $90,000 William Dietrich Administration $30,000

    Julie Johnson Sales and Marketing $75,000 Andy Schmidt Sales and Marketing $70,000

    Oscar Perry Sales and Marketing $45,000Larry Wilton Sales and Marketing $30,000Shelly Walton Sales and Marketing $30,000Bobby Harris Sales and Marketing $30,000

    Allison Dietrich Sales and Marketing $30,000Henry Parry Sales and Marketing $30,000Bev George Sales and Marketing $30,000Erika Deeter Accounting $60,000John Dennis Accounting $46,000Stan Marsh Accounting $45,000Tina Walton Logistics and Supply $80,000Doug Harris Logistics and Supply $50,000

    Vincent Johns Logistics and Supply $50,000Billy Yolto Logistics and Supply $30,000Eric Kraus Logistics and Supply $27,000Thomas Walton Logistics and Supply $25,000

  • 7/27/2019 Uko Ugs Ql Master Class

    17/78

    Employee Department Salary Rank-------------------- -------------------- --------- ----Rachel Middleton Administration $850,000 1

    Allison Ballinger Administration $90,000 2 William Dietrich Administration $30,000 3

    Julie Johnson Sales and Marketing $75,000 1 Andy Schmidt Sales and Marketing $70,000 2

    Oscar Perry Sales and Marketing $45,000 3Larry Wilton Sales and Marketing $30,000 4Shelly Walton Sales and Marketing $30,000 4Bobby Harris Sales and Marketing $30,000 4

    Allison Dietrich Sales and Marketing $30,000 4Henry Parry Sales and Marketing $30,000 4Bev George Sales and Marketing $30,000 4Erika Deeter Accounting $60,000 1John Dennis Accounting $46,000 2Stan Marsh Accounting $45,000 3Tina Walton Logistics and Supply $80,000 1Doug Harris Logistics and Supply $50,000 2

    Vincent Johns Logistics and Supply $50,000 2Billy Yolto Logistics and Supply $30,000 3Eric Kraus Logistics and Supply $27,000 4Thomas Walton Logistics and Supply $25,000 5

  • 7/27/2019 Uko Ugs Ql Master Class

    18/78

    Employee Department Salary Rank-------------------- -------------------- --------- ----Rachel Middleton Administration $850,000 1

    Allison Ballinger Administration $90,000 2 William Dietrich Administration $30,000 3

    Julie Johnson Sales and Marketing $75,000 1 Andy Schmidt Sales and Marketing $70,000 2

    Oscar Perry Sales and Marketing $45,000 3Bev George Sales and Marketing $30,000 4Larry Wilton Sales and Marketing $30,000 4Henry Parry Sales and Marketing $30,000 4

    Allison Dietrich Sales and Marketing $30,000 4Shelly Walton Sales and Marketing $30,000 4Bobby Harris Sales and Marketing $30,000 4Erika Deeter Accounting $60,000 1John Dennis Accounting $46,000 2Stan Marsh Accounting $45,000 3Tina Walton Logistics and Supply $80,000 1Doug Harris Logistics and Supply $50,000 2

    Vincent Johns Logistics and Supply $50,000 2Billy Yolto Logistics and Supply $30,000 3Eric Kraus Logistics and Supply $27,000 4Thomas Walton Logistics and Supply $25,000 5

  • 7/27/2019 Uko Ugs Ql Master Class

    19/78

  • 7/27/2019 Uko Ugs Ql Master Class

    20/78

    SQL*Plus DemoReport on sales by salesperson inNov/Dec 2008 for Boots, and Tents andrank according to type and monthlysales by type

  • 7/27/2019 Uko Ugs Ql Master Class

    21/78

    Analytical Functions are allowed inORDER BY clause

    PARTITIONORDER BY

    May not be the clearest of codeQuery column name

    Sort order from clause

    ORDER BY

  • 7/27/2019 Uko Ugs Ql Master Class

    22/78

    SQL*Plus DemoReport on all employees salaries bydepartment and sort by their rankingwithin the department

  • 7/27/2019 Uko Ugs Ql Master Class

    23/78

    Aggregate ValuesYou can RANK on an aggregate valueAvoids using subquery to generateaggregate value and then applyingfunction

    RANK() OVER

    ORDER BY (SUM(SALARY) DESC)

  • 7/27/2019 Uko Ugs Ql Master Class

    24/78

    SQL*Plus DemoReport on the total salary in eachdepartment and rank by total salary

  • 7/27/2019 Uko Ugs Ql Master Class

    25/78

    Multiple FunctionsEach function is applied according to itsown clauseMultiple partitions can be used in asingle statement

  • 7/27/2019 Uko Ugs Ql Master Class

    26/78

    SQL*Plus DemoReport on employee salary and rank bydepartment and job title

  • 7/27/2019 Uko Ugs Ql Master Class

    27/78

    Other ValueLAG previous valueLEAD subsequent valueFIRST first value in order

    LAST last value in order

  • 7/27/2019 Uko Ugs Ql Master Class

    28/78

    Lag/LeadFind a value in a row before/after thecurrent row

    LAG/LEAD(expression)Expression can be data or function

    Optional ParametersOffset relative position (default is 1)

    Default value if offset not in partition(default is NULL)

  • 7/27/2019 Uko Ugs Ql Master Class

    29/78

    SQL*Plus DemoWhat is the monthly difference in salesfor Allison Dietrich in 2007?What is the monthly difference in salesfor Henry Parry and Allison Dietrich in2007?What is the difference in monthly sales

    from Allison Dietrich compared to theprevious year?

  • 7/27/2019 Uko Ugs Ql Master Class

    30/78

    WindowingRange of rows relative to the currentrow

    Default is all rows in result setDynamic based on row or data

    1 per functionMultiple windows per statement

    Will not span a partition boundary

  • 7/27/2019 Uko Ugs Ql Master Class

    31/78

    SQL*Plus DemoWhat were the 2007 monthly sales forAllison Dietrich and what was runningtotal for the year?What were the 2007 monthly sales forAllison Dietrich and what was the 3month average (the current month is

    the mid point)?

  • 7/27/2019 Uko Ugs Ql Master Class

    32/78

    Employee Month Sales Average Sales-------------------- ---------- ------------- -------------

    Allison Dietrich 01-JAN-07 $68,758,209 $63,779,320 Allison Dietrich 01-FEB-07 $58,800,431 $59,911,777 Allison Dietrich 01-MAR-07 $52,176,691 $54,751,431 Allison Dietrich 01-APR-07 $53,277,171 $54,504,683 Allison Dietrich 01-MAY-07 $58,060,187 $57,630,093

    Allison Dietrich 01-JUN-07 $61,552,921 $58,068,567 Allison Dietrich 01-JUL-07 $54,592,592 $61,304,622 Allison Dietrich 01-AUG-07 $67,768,354 $57,979,014 Allison Dietrich 01-SEP-07 $51,576,097 $57,100,083 Allison Dietrich 01-OCT-07 $51,955,799 $52,414,127

    Allison Dietrich 01-NOV-07 $53,710,486 $56,141,177 Allison Dietrich 01-DEC-07 $62,757,245 $53,555,005

  • 7/27/2019 Uko Ugs Ql Master Class

    33/78

    Employee Month Sales Average Sales-------------------- ---------- ------------- -------------

    Allison Dietrich 01-JAN-07 $68,758,209 $63,779,320 Allison Dietrich 01-FEB-07 $58,800,431 $59,911,777 Allison Dietrich 01-MAR-07 $52,176,691 $54,751,431 Allison Dietrich 01-APR-07 $53,277,171 $54,504,683 Allison Dietrich 01-MAY-07 $58,060,187 $57,630,093 Allison Dietrich 01-JUN-07 $61,552,921 $58,068,567

    Allison Dietrich 01-JUL-07 $54,592,592 $61,304,622 Allison Dietrich 01-AUG-07 $67,768,354 $57,979,014 Allison Dietrich 01-SEP-07 $51,576,097 $57,100,083 Allison Dietrich 01-OCT-07 $51,955,799 $52,414,127 Allison Dietrich 01-NOV-07 $53,710,486 $56,141,177 Allison Dietrich 01-DEC-07 $62,757,245 $53,555,005

    - 10

    +1

  • 7/27/2019 Uko Ugs Ql Master Class

    34/78

    Employee Month Sales Average Sales-------------------- ---------- ------------- -------------

    Allison Dietrich 01-JAN-07 $68,758,209 $63,779,320 Allison Dietrich 01-FEB-07 $58,800,431 $59,911,777 Allison Dietrich 01-MAR-07 $52,176,691 $54,751,431 Allison Dietrich 01-APR-07 $53,277,171 $54,504,683 Allison Dietrich 01-MAY-07 $58,060,187 $57,630,093 Allison Dietrich 01-JUN-07 $61,552,921 $58,068,567 Allison Dietrich 01-JUL-07 $54,592,592 $61,304,622 Allison Dietrich 01-AUG-07 $67,768,354 $57,979,014 Allison Dietrich 01-SEP-07 $51,576,097 $57,100,083 Allison Dietrich 01-OCT-07 $51,955,799 $52,414,127 Allison Dietrich 01-NOV-07 $53,710,486 $56,141,177

    Allison Dietrich 01-DEC-07 $62,757,245 $53,555,005

    - 10

    +1

  • 7/27/2019 Uko Ugs Ql Master Class

    35/78

    Employee Month Sales Average Sales-------------------- ---------- ------------- -------------

    Allison Dietrich 01-JAN-07 $68,758,209 $63,779,320 Allison Dietrich 01-FEB-07 $58,800,431 $59,911,777 Allison Dietrich 01-MAR-07 $52,176,691 $54,751,431 Allison Dietrich 01-APR-07 $53,277,171 $54,504,683 Allison Dietrich 01-MAY-07 $58,060,187 $57,630,093 Allison Dietrich 01-JUN-07 $61,552,921 $58,068,567 Allison Dietrich 01-JUL-07 $54,592,592 $61,304,622 Allison Dietrich 01-AUG-07 $67,768,354 $57,979,014 Allison Dietrich 01-SEP-07 $51,576,097 $57,100,083 Allison Dietrich 01-OCT-07 $51,955,799 $52,414,127 Allison Dietrich 01-NOV-07 $53,710,486 $56,141,177

    Allison Dietrich 01-DEC-07 $62,757,245 $53,555,005

    - 10

    +1

  • 7/27/2019 Uko Ugs Ql Master Class

    36/78

    Employee Month Sales Average Sales

    -------------------- ---------- ------------- ------------- Allison Dietrich 01-JAN-07 $68,758,209 $63,779,320 Allison Dietrich 01-FEB-07 $58,800,431 $59,911,777 Allison Dietrich 01-MAR-07 $52,176,691 $54,751,431 Allison Dietrich 01-APR-07 $53,277,171 $54,504,683 Allison Dietrich 01-MAY-07 $58,060,187 $57,630,093 Allison Dietrich 01-JUN-07 $61,552,921 $58,068,567 Allison Dietrich 01-JUL-07 $54,592,592 $61,304,622 Allison Dietrich 01-AUG-07 $67,768,354 $57,979,014 Allison Dietrich 01-SEP-07 $51,576,097 $57,100,083 Allison Dietrich 01-OCT-07 $51,955,799 $52,414,127

    Allison Dietrich 01-NOV-07 $53,710,486 $56,141,177 Allison Dietrich 01-DEC-07 $62,757,245 $53,555,005- 10+1

  • 7/27/2019 Uko Ugs Ql Master Class

    37/78

    OffsetDEFAULT equivalent to

    ROWS BETWEEN UNBOUNDED PRECEEDING AND UNBOUNDED FOLLOWING

    RANGE BETWEEN UNBOUNDED PRECEEDING AND UNBOUNDED FOLLOWING

    *Within the PARTITION!

  • 7/27/2019 Uko Ugs Ql Master Class

    38/78

    SQL*Plus DemoWhat were the 2007 monthly sales forAllison Dietrich and Henry Parry andwhat was running total for the year?

  • 7/27/2019 Uko Ugs Ql Master Class

    39/78

    Logical OffsetBases the offset on data, not rowcountsUseful if data is missing

  • 7/27/2019 Uko Ugs Ql Master Class

    40/78

    SQL*Plus DemoWhat were the 2007 monthly sales forAllison Dietrich and what was the 3month average (the current month isthe mid point)?

  • 7/27/2019 Uko Ugs Ql Master Class

    41/78

    Filter limitationAnalytical Functions cannot be used tofilter records

    WHERE (filter predicate) is appliedBEFORE functions are processed

  • 7/27/2019 Uko Ugs Ql Master Class

    42/78

    SQL*Plus DemoWhat were the top 3 Salespeople for2007?

  • 7/27/2019 Uko Ugs Ql Master Class

    43/78

    SELECT

    WHERE/joins

    GROUP BY/HAVING

    Partitioning

    Ordering

    Windowing

    ApplyFunctions

    ORDER BY

    SELECT name emp_name, SUM(sales_month) year_sales

    FROM monthly_sales_person WHERE order_month

    BETWEEN '01-JAN-07' AND '01-DEC-07' AND RANK() OVER

    (ORDER BY SUM(sales_month) DESC) >= 3

    GROUP BY name

    RANK() OVER

    (ORDER BY SUM(sales_month) DESC)rank

    ORDER BY year_sales DESC

  • 7/27/2019 Uko Ugs Ql Master Class

    44/78

    Thenhow can I create a Top NQuery?

    Subquery

    With WITH **no this is not a typo

    WI TH AS ( subquer y)SELECT * FROM

    WI TH AS ( subquer y) , AS ( subquer y)

    SELECT * FROM

  • 7/27/2019 Uko Ugs Ql Master Class

    45/78

    WITHCreates a named subquerySubquery can be referenced insubsequent query(ies)

    Can be nestedOpinion

    Cleaner, easier to read code than multiple

    nested inline subqueries Develop using modular approach

  • 7/27/2019 Uko Ugs Ql Master Class

    46/78

    SQL*Plus DemoWhat were the top 3 Salespeople for2007?

  • 7/27/2019 Uko Ugs Ql Master Class

    47/78

  • 7/27/2019 Uko Ugs Ql Master Class

    48/78

    Regular ExpressionsDocumented in SQL reference,

    Application Developers Guide -FundamentalsPattern Matching and Processing

    Several different ways to match the samedataLearning Pattern Matching is critical

  • 7/27/2019 Uko Ugs Ql Master Class

    49/78

    Similar to older functions/conditionLIKE REGEXP_LIKEINSTR REGEXP_INSTRSUBSTR REGEXP_SUBSTRREPLACE REGEXP_REPLACE

  • 7/27/2019 Uko Ugs Ql Master Class

    50/78

    All about patternsRecognizeDescribeManipulate

    Know your dataData drives the pattern

    Beware of data that does not fit thepattern

  • 7/27/2019 Uko Ugs Ql Master Class

    51/78

    REGEXP_LIKECondition that evaluates to TRUE or

    FALSEWHERE regexp_like ( )No need for % wildcardMatches pattern anywhere in string

    Use to locate data before modifying it

  • 7/27/2019 Uko Ugs Ql Master Class

    52/78

    SQL*Plus DemoLocate employees named Parry or Perry

    Locate employees with dd in their name

  • 7/27/2019 Uko Ugs Ql Master Class

    53/78

    Format and OptionsREGEXP_LIKE (string, pattern,

    parameters)String character string or character dataPattern regular expression

    Parameters *i case insensitive searchingc case sensitive searchingn allow . to match newline characterm source string is multiple linesx ignore whitespace

  • 7/27/2019 Uko Ugs Ql Master Class

    54/78

    Patterns. Any character (one or more)

    * 0 or more of the previous? 0 or 1 of the previous

    + 1 or more of the previous^ Beginning of the line$ End of the line

  • 7/27/2019 Uko Ugs Ql Master Class

    55/78

    {m} Exact m of previous

    {m,} M or more of previous{m,n} Between m and n of previous

    [] Characters to match[^] Characters not to match() Group/sub-expression of characters| Or

  • 7/27/2019 Uko Ugs Ql Master Class

    56/78

    SQL*Plus DemoLocate employees with h in their name

    Locate employees with h surrounded byat least one character one each sideLocate employees whose name startswith hLocate employees whose name ends with

    h

  • 7/27/2019 Uko Ugs Ql Master Class

    57/78

    Locate employees whose name starts

    with THLocate employees whose name startswith T or HLocate employees with ll in their nameLocate employees with ll in their name,

    but exclude lly

  • 7/27/2019 Uko Ugs Ql Master Class

    58/78

    Matching ListsA-Z

    a-z0-9a-zA-Z0-9

    UPPERCASE alpha lowercase alpha Numbers Alphanumeric

  • 7/27/2019 Uko Ugs Ql Master Class

    59/78

    Character Classes[:alpha:]

    [:lower:][:upper:][:digit:][:alnum:][:cntrl:]

    Alphabetic lowercase alpha UPPERCASE alpha Numbers Alphanumeric Control

  • 7/27/2019 Uko Ugs Ql Master Class

    60/78

  • 7/27/2019 Uko Ugs Ql Master Class

    61/78

    Finding PatternsLocate comments with Phone Numbers

    US 10 numbers (3 3 4)Variable formats( 783) 555- 2327

    872. 555. 8730872 555 8730

    3245558728

    What is the pattern?

  • 7/27/2019 Uko Ugs Ql Master Class

    62/78

    Lets build the pattern to match

    Numeric format 3 3 4[0-9]{3} [0-9]{3} [0-9]{4}[[:digit:]]{3} [[:digit:]]{3} [[:digit:]]{4}

    What about between the

  • 7/27/2019 Uko Ugs Ql Master Class

    63/78

    What about between thenumbers?

    ( 783) 555- 2327

    872. 555. 8730872 555 8730

    3245558728

    (\(| )###(\)| |.)###(-| |.)####

  • 7/27/2019 Uko Ugs Ql Master Class

    64/78

    ? Match 0 or 1

    {0,} Match 0 or more( 783) 555- 2327872. 555. 8730

    872 555 87303245558728

    (\(| )?###(\)| |.)?###(-| |.)?####(\(| ){0,}###(\)| |.){0,}###(-| |.){0,}####

  • 7/27/2019 Uko Ugs Ql Master Class

    65/78

    SQL*Plus DemoFind all the order comments that

    contain a US telephone number

  • 7/27/2019 Uko Ugs Ql Master Class

    66/78

    REGEXP_SUBSTRReturn parts of a string based on the

    patternVery similar to SUBSTRCompliment to REGEXP_LIKE

    You can see what you match

  • 7/27/2019 Uko Ugs Ql Master Class

    67/78

    (string, pattern, position, occurrence,

    parameters)String character string or character dataPattern regular expression

    Position place to start in the stringOccurrence which occurrence of thepattern to replace

  • 7/27/2019 Uko Ugs Ql Master Class

    68/78

    SQL*Plus DemoFind all the US telephone numbers in

    order comments

  • 7/27/2019 Uko Ugs Ql Master Class

    69/78

    Positions and OccurrencesStart searching at Y position

    Default is 1-N is not supported

    Match the Nth occurrenceDefault is 1-N is not supported

  • 7/27/2019 Uko Ugs Ql Master Class

    70/78

    SQL*Plus DemoFind the second occurrence of the

    pattern . NNN Find the first occurrence of thepattern . NNN after character 47

  • 7/27/2019 Uko Ugs Ql Master Class

    71/78

    REGEXP_INSTRReturn location of a string based on the

    patternVery similar to INSTRReturn option is different

    Compliment to REGEXP_LIKEYou can see where you match

  • 7/27/2019 Uko Ugs Ql Master Class

    72/78

    (string, pattern, position, occurrence,

    return_option, parameters)String character string or character dataPattern regular expression

    Position place to start in the stringOccurrence which occurrence of thepattern to replace

    Return Option return the first (0) or last(1) character position of the pattern

  • 7/27/2019 Uko Ugs Ql Master Class

    73/78

    SQL*Plus DemoFind the starting position of the

    telephone numbersFind the ending position of thetelephone numbers

  • 7/27/2019 Uko Ugs Ql Master Class

    74/78

    REGEXP_REPLACEReplaces parts of a string based on the

    patternVery powerful!First introduction to regular expressions

  • 7/27/2019 Uko Ugs Ql Master Class

    75/78

    (string, pattern, replace_string,

    position, occurrence, parameters)String character string or character dataPattern regular expression

    Replace_string character string to use asreplacementPosition place to start in the string

    Occurrence which occurrence of thepattern to replace

  • 7/27/2019 Uko Ugs Ql Master Class

    76/78

    SQL*Plus DemoReplace multiple spaces in comments

    with single spaces

  • 7/27/2019 Uko Ugs Ql Master Class

    77/78

  • 7/27/2019 Uko Ugs Ql Master Class

    78/78

    Daniel Fink

    www. opt i mal dba. comdani el . f i nk@opt i mal dba. com