Eliminate Rows Having a Letter and Number Combination

I am currently using Oracle9i Enterprise Edition Release 9.2.0.4.0. I have a table with following data

Table 1 (Sample data)

a12345
A123423
g13452
G452323
h34423
r34323
b23232n232323

I am currently using this as a subquery in one of the query. As per a new request I have to now exclude all values which start with h, b or n followed by numeric values. So end result the subquery should give me is

Table 1 (Sample data)

a12345
A123423
g13452
G452323
r34323

I am little stumped on this for now. Could not get it right in my query. Can anyone please advise here. Let me know if any more information is needed from my side.

Note: The starting character in all values can sometimes in “lower case” or sometimes in “upper case”.

Interesting problem, although it would have been helpful had the OP provided the DDL and DML to create the test case. Let’s see if there is a hard way to solve this problem:

If there is a 1 in both of the right-most columns, then the row should be eliminated. What is the easiest way to tell if there is a 1 in both columns? Multiply the column values together, and if we receive a product of 1 then the row should be excluded:

Use the ASCII function to find the ASCII value of the second character

Subtract 47 from the ASCII value for the second character

If the difference is greater than 0, then:
** Subtract 58 from that ASCII value

If the difference is less than 0, then we found an ASCII value between 48 and 57 – therefore the second character must be a number
** Return the number 1 if the ASCII value is between 48 and 57, otherwise return 0

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Maxim Demenko offered the following as a solution to the problem which uses the RTRIM function:

Notice in the above that the TEST column shows that the RTRIM function eliminated everything to the right of the first digit, including that first digit. Then, his solution simply determines if what is left (in the TEST column) is one of h, b, or n, and if it is, the row is eliminated.

Mark Powell offered the following method using the TRANSLATE function:

Here is a solution that uses a translate function. My result vary because I could not remember the actual starting letters specified by the OP as I do not have access to Oracle and the forum at the same time. I made my solution case sensitive and used “b,g, and h”. I added two rows to ensure at least one row that started with one of the exclude letters when followed by digits whould appear in the output.

The above assumes that all the data is of the form Letter || digits and that no data with mixed letters and digits where the presence of letters should cause the data to not be excluded. The following would handle data with those rules using something like h123x as a test case.

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:
<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: