6. Working with Strings
SQL is not designed to perform complex string manipulation
Walking a String
We want to traverse a string to return each character as a row.
Embedding Quotes Within String Literals
We want to embed quote marks within string literals.
Counting the Occurrences of a Character in a String
We want to count the number of times a character or substring occurs within a given string. Consider the following string. We want to determine how many commas are in the string.
10,CLARK,MANAGER
Removing Unwanted Characters from a String
We want to remove specific characters from data. For example, we want to remove all zeros and vowels.
Separating Numeric and Character Data
We have numeric data stored with character data together in one column. We want to separate the character data from the numeric data.
Sample Data
SMITH800 ALLEN1600 WARD1250 JONES2975
Sample Output
ENAME SAL SMITH 800 ALLEN 1600 WARD 1250 JONES 2975
Determining Whether a String Is Alphanumeric
We want to return rows from a table only when a column contains no characters other than numbers and letters.
Sample Data
Extracting Initials from a Name
We want to convert a full name into initials.
Input - Stewie Griffin Output - S.G.
Step 1: S##### G###### Step 2: S G Step 3: S.G Step 4: S.G. (append . at the end)
Ordering by Parts of a String
We want to order result set based on a substring. For example, ordered based on the last two characters of each name.
Ordering by a Number in a String
We want to order the result set based on a number within a string
Sample Data
Creating a Delimited List from Table Rows
We want to return table rows as values in a delimited list rather than in vertical columns as they usually appear.
Sample Data
DEPTNO EMPS
10 CLARK 10 KING 10 MILLER 20 SMITH 20 ADAMS 20 FORD 20 SCOTT 20 JONES 30 ALLEN
Sample Output
DEPTNO EMPS
10 CLARK,KING,MILLER 20 SMITH,JONES,SCOTT,ADAMS,FORD 30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
The purpose of the rank (aliased RN in the query) is to allow us to walk the tree. Since the function ROW_NUMBER generates an enumeration starting from one with no duplicates or gaps, just subtract one (from the current value) to reference a prior (or parent) row. For example, the number prior to 3 is 3 minus 1, which equals 2. In this context, 2 is the parent of 3.
Output
Output of inner subquery
Converting Delimited Data into a Multivalued IN-List
We have delimited data that we want to pass to the IN-list iterator of a WHERE clause. Consider the following string: 7654,7698,7782,7788
We would like to use the string in a WHERE clause, but the following SQL fails because EMPNO is a numeric column:
This SQL fails because, while EMPNO is a numeric column, the IN list is composed of a single string value. We want that string to be treated as a comma-delimited list of numeric values like 7654,7698,7782,7788
.
Explanation
Step 1: Walk the string
The number of rows returned represents the number of values in the list. The strings are parsed using SUBSTR and INSTR. POS is used to locate the nth occurrence of the delimiter in each string. By enclosing the strings in commas, no special checks are necessary to determine the beginning or end of a string.
The final step is to remove the trailing comma from each value, cast it to a number, and plug it into a subquery.
Alphabetizing a String
We want alphabetize the individual characters within strings in your tables.
Sample Date
ENAME
ADAMS ALLEN BLAKE
Result
OLD_NAME NEW_NAME
ADAMS AADMS ALLEN AELLN BLAKE ABEKL
Explanation
Inline view result by walking the string and then imposing order on those characters. The rest of the query attach the names back together.
The next step is to take the alphabetized characters and rebuild each name. This is done with the function SYS_CONNECT_BY_PATH by appending each character to the ones before it
The final step is to keep only the strings that have the same length as the names they were built from.
Identifying Strings That Can Be Treated as Numbers
We have a column that is defined to hold character data.
Sample Data
CL10AR KI10NG MI10LL 7369 7566
We want to return rows that are numbers only, or that contain at least one number. If the numbers are mixed with character data, we want to remove the characters and return only the numbers.
Output
10 10 10 7369 7566
Explanation
Extracting the nth Delimited Substring
We want to extract a specified, delimited substring from a string.
Sample Data
Sample Output (We want to extract the second name in each row)
Parsing an IP Address
We want to parse an IP address’s fields into columns
Comparing Strings by Sound
SQL provides a way to represent the way words sound, which allows you to find strings that sound the same even though the underlying characters aren’t identical.
For example, you have a list of authors’ names,
Sample Output
Finding Text Not Matching a Pattern
We have a text field that contains some structured text values (e.g., phone numbers), and want to find occurrences where those values are structured incorrectly. We want to list rows having invalidly formatted phone numbers.
Record with 7369 is invalid because its phone number uses two different separator characters.
Sample Output
Last updated