book

Oracle SQL Recipes: A Problem-Solution Approach

Name: Oracle SQL Recipes: A Problem-Solution Approach
ISBN: 9781430225096

by Grant Allen, Bob Bryla, Darl Kuhn

November 2009

Intermediate to advanced

576 pages

12h 25m

English

Apress

Read now

Unlock full access

Copyright
About the Authors
About the Technical Reviewer
Acknowledgements
Introduction
Who This Book Is ForHow This Book Is StructuredChoosing ToolsWorking With Example Data for SQL RecipesConventionsContacting the Authors
1. Foundation of Data Manipulation
1. The Basics
1.1. Retrieving Data from a Table1.2. Selecting All Columns from a Table1.3. Sorting Your Results1.4. Adding Rows to a Table1.5. Copying Rows from One Table to Another1.6. Copying Data in Bulk from One Table to Another1.7. Changing Values in a Row1.8. Updating Multiple Fields with One Statement1.9. Removing Unwanted Rows from a Table1.10. Removing All Rows from a Table1.11. Selecting from the Results of Another Query1.12. Basing a Where Condition on a Query1.13. Finding and Eliminating NULLs in Queries1.14. Sorting as a Person Expects1.15. Enabling Other Sorting and Comparison Options1.16. Conditional Inserting or Updating Based on Existence
2. Summarizing and Aggregating Data
2.1. Summarizing the Values in a Column2.2. Summarizing Data for Different Groups2.3. Grouping Data by Multiple Fields2.4. Ignoring Groups in Aggregate Data Sets2.5. Aggregating Data at Multiple Levels2.6. Using Aggregate Results in Other Queries2.7. Counting Members in Groups and Sets2.8. Finding Duplicates and Unique Values in a Table2.9. Calculating Totals and Subtotals2.10. Building Your Own Aggregate Function2.11. Accessing Values from Subsequent or Preceding Rows2.12. Assigning Ranking Values to Rows in a Query Result2.13. Finding First and Last Values within a Group2.14. Performing Aggregations over Moving Windows2.15. Removing Duplicate Rows Based on a Subset of Columns2.16. Finding Sequence Gaps in a Table
3. Querying from Multiple Tables
3.1. Joining Corresponding Rows from Two or More Tables3.2. Stacking Query Results Vertically3.3. Writing an Optional Join3.4. Making a Join Optional in Both Directions3.5. Removing Rows Based on Data in Other Tables3.6. Finding Matched Data Across Tables3.7. Joining on Aggregates3.8. Finding Missing Rows3.9. Finding Rows that Tables Do Not Have in Common3.10. Generating Test Data3.11. Updating Rows Based on Data in Other Tables3.12. Manipulating and Comparing NULLs in Join Conditions
4. Creating and Deriving Data
4.1. Deriving New Columns4.2. Returning Nonexistent Rows4.3. Changing Rows into Columns4.4. Pivoting on Multiple Columns4.5. Changing Columns into Rows4.6. Concatenating Data for Readability4.7. Translating Strings to Numeric Equivalents4.8. Generating Random Data4.9. Creating a Comma-Separated Values File

5. Common Query Patterns
5.1. Changing Nulls into Real Values5.2. Sorting on Null Values5.3. Paginating Query Results5.4. Testing for the Existence of Data5.5. Conditional Branching In One SQL Statement5.6. Conditional Sorting and Sorting By Function5.7. Overcoming Issues and Errors when Subselects Return Unexpected Multiple Values5.8. Converting Numbers Between Different Bases5.9. Searching for a String Without Knowing the Column or Table5.10. Predicting Data Values and Trends Beyond a Series End5.11. Explicitly (Pessimistically) Locking Rows for an Update5.12. Synchronizing the Contents of Two Tables
2. Data Types and Their Problems
6. Working with Date and Time Values
6.1. Converting Datetime Values into Readable Strings6.2. Converting Strings to Datetime Values6.3. Detecting Overlapping Date Ranges6.4. Automatically Tracking Date and Time for Data Changes6.5. Generating a Gapless Time Series from Data with Gaps6.6. Converting Dates and Times Between Time Zones6.7. Detecting Leap Years6.8. Computing the Last Date in a Month6.9. Determining the First Date or Day in a Month6.10. Calculating the Day of the Week6.11. Grouping and Aggregating by Time Periods6.12. Finding the Difference Between Two Dates or Date Parts6.13. Determining the Dates of Easter for Any Year6.14. Calculating "X Day Active" Users for a Web Site
7. Strings
7.1. Searching for a Substring7.2. Extracting a Substring7.3. Single-Character String Substitutions7.4. Searching for a Pattern7.5. Extracting a Pattern7.6. Counting Patterns7.7. Replacing Text in a String7.8. Speeding Up String Searches
8. Working with Numbers
8.1. Converting Between String and Numeric Data Types8.2. Converting Between Numeric Data Types8.3. Choosing Data Type Precision and Scale8.4. Performing Calculations Correctly with Non-Numbers and Infinite Numbers8.5. Validating Numbers in Strings8.6. Generating Consecutive Numbers8.7. Generating Numbers to a Formula or Pattern8.8. Handling Nulls in Numeric Calculations8.9. Automatically Rounding Numbers8.10. Automatically Generating Lists of Numbers
3. Your Development Environment
9. Managing Transactions
9.1. Partially Rolling Back a Transaction9.2. Identifying Blocking Transactions9.3. Optimizing Row and Table Locking9.4. Avoiding Deadlock Scenarios9.5. Deferring Constraint Validation9.6. Ensuring Read-Consistency Across a Transaction9.7. Managing Transaction Isolation Levels
10. Data Dictionary
10.1. Graphical Tools vs. SQL10.2. Data Dictionary Architecture10.3. Displaying User Information10.4. Determining the Tables You Can Access10.5. Displaying a Table's Disk Space Usage10.6. Displaying Table Row Counts10.7. Displaying Indexes for a Table10.8. Showing Foreign Key Columns Not Indexed10.9. Displaying Constraints10.10. Showing Primary Key and Foreign Key Relationships10.11. Displaying Object Dependencies10.12. Displaying Synonym Metadata10.13. Displaying View Text10.14. Displaying Database Code10.15. Displaying Granted Roles10.16. Displaying Object Privileges10.17. Displaying System Privileges
4. Data Dictionary
11. Common Reporting Problems
11.1. Avoiding Repeating Rows in Reports11.2. Parameterizing a SQL Report11.3. Returning Detail Columns in Grouped Results11.4. Sorting Results into Equal-Size Buckets11.5. Creating Report Histograms11.6. Filtering Results by Relative Rank11.7. Comparing Hypotheses on Sets of Data11.8. Graphically Representing Data Distribution with Text11.9. Producing Web-Page Reports Directly from the Database
12. Cleansing Data
12.1. Detecting Duplicate Rows12.2. Removing Duplicate Rows12.3. Determining if Data Can Be Loaded as Numeric12.4. Determining if Data Can Be Loaded as a Date12.5. Performing Case-Insensitive Queries12.6. Obfuscating Values12.7. Dropping All Indexes12.8. Disabling Constraints12.9. Disabling Triggers12.10. Removing Data from a Table12.11. Showing Differences in Schemas
13. Tree-Structured Data
13.1. Traversing Hierarchical Data from Top to Bottom13.2. Sorting Nodes Within a Hierarchical Level13.3. Generating Pathnames from Hierarchical Tables13.4. Identifying Leaf Data in a Hierarchical Table13.5. Detecting Cycles in Hierarchical Data13.6. Generating a Fixed Number of Sequential Primary Keys
14. Working with XML Data
14.1. Translating SQL to XML14.2. Storing XML in Native Form14.3. Shredding XML for Relational Use14.4. Extracting Key XML Elements from an XML Document14.5. Generating Complex XML Documents14.6. Validating XML Schema14.7. Changing XML in Place
15. Partitioning
15.1. Determining if a Table Should be Partitioned15.2. Partitioning by Range15.3. Partitioning by List15.4. Partitioning by Hash15.5. Partitioning a Table in Multiple Ways15.6. Creating Partitions on Demand15.7. Partitioning by Referential Constraints15.8. Partitioning on a Virtual Column15.9. Application-Controlled Partitioning15.10. Configuring Partitions with Tablespaces15.11. Automatically Moving Updated Rows15.12. Partitioning an Existing Table15.13. Adding a Partition to a Partitioned Table15.14. Exchanging a Partition with an Existing Table15.15. Renaming a Partition15.16. Splitting a Partition15.17. Merging Partitions15.18. Dropping a Partition15.19. Removing Rows from a Partition15.20. Generating Statistics for a Partition15.21. Creating an Index that Maps to a Partition (LocalIndex)15.22. Creating an Index with Its Own Partitioning Scheme (Global Index)
16. LOBs
16.1. Loading Large Documents into CLOB Columns16.2. Loading Image Data into BLOB Columns16.3. Using SQL*Loader to Bulk-Load Large Objects16.4. Accessing Large Objects Using HTTP16.5. Making External Large Objects (BFILEs) Available to the Database16.6. Deleting or Updating LOBs in a Database Table
5. Administration
17. Database Administration
17.1. Creating a Database17.2. Dropping a Database17.3. Verifying Connection Information17.4. Creating Tablespaces17.5. Dropping a Tablespace17.6. Adjusting Tablespace Size17.7. Limiting Database Resources per Session17.8. Associating a Group of Privileges17.9. Creating Users17.10. Dropping Users17.11. Modifying Passwords17.12. Enforcing Password Complexity
18. Object Management
18.1. Creating a Table18.2. Storing Data Temporarily18.3. Moving a Table18.4. Renaming Objects18.5. Dropping a Table18.6. Undropping a Table18.7. Creating an Index18.8. Creating a Function-Based Index18.9. Creating a Bitmap Index18.10. Creating an Index-Organized Table18.11. Creating a View18.12. Creating an Alternate Name for an Object18.13. Enforcing Unique Rows in a Table18.14. Ensuring Lookup Values Exist18.15. Checking Data for a Condition18.16. Creating a Connection Between Databases18.17. Creating an Auto-incrementing Value
19. Monitoring and Tuning
19.1. Monitoring Real-Time SQL Execution Statistics19.2. Displaying a Query's Progress in the Execution Plan19.3. Determining How Much SQL Work Is Left19.4. Identifying Resource-Intensive SQL Statements19.5. Using Oracle Performance Reports to Identify Resource-Intensive SQL19.6. Using the Operating System to Identify Resource-Intensive Queries19.7. Displaying an Execution Plan Using AUTOTRACE19.8. Generating an Execution Plan Using DBMS_XPLAN19.9. Tracing All SQL Statements for a Session19.10. Interpreting an Execution Plan19.11. Obtaining SQL Tuning Advice19.12. Forcing Your Own Execution Plan on a Query19.13. Viewing Optimizer Statistics19.14. Generating Statistics
20. Database Troubleshooting
20.1. Determining Causes of Database Problems20.2. Displaying Open Cursors20.3. Determining If Online Redo Logs Are Sized Properly20.4. Determining If Undo Is Sized Properly20.5. Determining If Temporary Tablespace Is Sized Correctly20.6. Displaying Tablespace Fullness20.7. Showing Object Sizes20.8. Monitoring Index Usage20.9. Auditing Object Usage20.10. Auditing at a Granular Level

Content preview from Oracle SQL Recipes: A Problem-Solution Approach

Chapter 7. Strings

Most likely, at least some of your database tables have some kind of string columns, whether they are CHAR, VARCHAR2, CLOB, or one of the equivalent multibyte versions. Once you have these columns in your tables, you generally have to do more than just display or capture the values in those columns. Thus, the focus of the recipes in this chapter is on searching, concatenating, and splitting string values in database columns.

Oracle has many built-in functions to search and manipulate strings. The INSTR function searches for a specified string within a second string. You can start the search anywhere within the second string, and you can also search for a specific occurrence of the first string as well.

To perform one-to-one substitutions ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Oracle Exadata Recipes: A Problem-Solution Approach

Publisher Resources

ISBN: 9781430225096Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Oracle SQL Recipes: A Problem-Solution Approach

by Grant Allen, Bob Bryla, Darl Kuhn

Chapter 7. Strings

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.