Extract alphanumeric value from varchar column

Question

I have a table which contains a column having alphanumeric values which is stored as a string. I have multiple values in that column having values such as F4737, 00Y778, PP0098, XXYYYZ etc.

I want to extract values starting with a series of F and must have numeric values in that row. Alphanumeric column is the unique column having unique values but the rest of the columns contain duplicate values in my table.

Futhermore, once these values are extracted I would like to pick up the max value from the duplicate row,for eg:

Suppose I have F4737 and F4700 as a unique Alphanumeric row, then F4737 must be extracted from it.

I have written a query like this but the numeric values are not getting extracted from this query:

select max(Alplanumeric) 
from Customers 
where Alplanumeric '%[F0-9]%

or

select max(Alplanumeric) 
from Customers 
where Alplanumeric like '%[0-9]%' 
and Alplanumeric like 'F%'**

I run the above query but I am only getting the F series if I remove the numeric part from the above query. How do I extract both, the F starting series as well as the numeric values included in that row?

If you want to do operations like this, then you should store the values in a junction table, with one row per customer and one row per code. — Gordon Linoff, Aug 09 '15 at 15:34
`LIKE` doesn't support regular expressions. You need to use `similar to`, the `~` operator or `regexp_matches()`. See the manual for details: http://www.postgresql.org/docs/current/static/functions-matching.html — , Aug 09 '15 at 15:37
I dont want to separate the values in a junction table or use regexp matches as it would increase the query execution . I tried this query : select max(CustomerName) from Customers where CustomerName like '%4%' and CustomerName like 'F%' , this one works but only for numeric value 4. I would like to add other numeric values too in this query. — user1538020, Aug 09 '15 at 15:41
How do you know it "*would increase the query execution*" if you don't try it? Btw: `like '%[0-9]%' ` will look for values that have a `[` followed by a `-` followed by a `]` anywhere in the column. — , Aug 09 '15 at 16:59
Your question is unclear. Please provide an exact table definition, some sample rows and the expected result. Best in the form of valid `CREATE TABLE` and `INSERT` statements. Among other things "max value" is ambiguous. Maximum of the numeric part (treated as number) or the text value? And *always* your version of Postgres. By "Alplanumeric" you mean "Alphanumeric" I suppose? — Erwin Brandstetter, Aug 09 '15 at 22:25

Erwin Brandstetter · Answer 1 · 2023-08-01T21:56:07.537

Going out on a limb, you might be looking for a query like this:

SELECT *, substring(alphanumeric, '^F(\d+)')::int AS nr
FROM   customers 
WHERE  alphanumeric ~ '^F\d+' 
ORDER  BY nr DESC NULLS LAST
        , alphanumeric
LIMIT  1;

The WHERE conditions is a regular expression match, the expression is anchored to the start, so it can use an index. Ideally:

CREATE INDEX customers_alphanumeric_pattern_ops_idx ON customers
(alphanumeric text_pattern_ops);

This returns the one row with the highest (extracted) numeric value in alphanumeric among rows starting with 'F' followed by one ore more digits.

About the index:

PostgreSQL LIKE query performance variations

About pattern matching:

Pattern matching with LIKE, SIMILAR TO or regular expressions

Ideally, you should store the leading text and the following numeric value in separate columns to make this more efficient. You don't necessarily need more tables like has been suggested.

Extract alphanumeric value from varchar column

1 Answers1