SQL MAX function and strings

Question

I have a column nr that contains strings in the format of 12345-12345. The numbers before and after the dash can be of any length. I would like to get the maximum value for nr taking into account only the part after the dash. I tried

SELECT MAX(nr) AS max_nr FROM table WHERE (nr LIKE '12345-%')

However, this works only for values < 10 (i.e. 12345-9 would be returned as max even if 12345-10 exists). I thought of removing the dash and doing a type conversion:

SELECT MAX(REPLACE(nr, '-', '')::int) AS max_nr FROM table WHERE (nr LIKE '12345-%')

However, this of course returns the result without the dash. What would be the best way to get the maximum value while including the dash and the number before the dash in the result?

PostgreSQL 9.1

Robbert · Accepted Answer · 2014-12-18 18:09:00Z

2

I'm no expert in PostGres, but you can use regexp_replace('foobarbaz', 'b..', 'X') to extract the string after the dash and then convert the number to int. The following query will retrieve only one row the nr from your table where the nr is like 12345-%, sorted by the number after the dash in descending order (largest number first).

SELECT nr
FROM table WHERE (nr LIKE '12345-%')
ORDER BY regexp_replace(nr, '^\d+-', '')::integer DESC
LIMIT 1

The regular expression above removes the leading digits and the dash, leaving only the last set of digits. For example, 54352-12345 would become 12345.

Official documentation.

And here is a SQL Fiddle illustrating it's use.

edited Dec 18, 2014 at 18:09

answered Dec 18, 2014 at 17:38

Robbert

6,6226 gold badges38 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Tolsto Over a year ago

How does this differ from my approach with REPLACE? As far as I can see, it also doesn't return the result including the dash.

Robbert Over a year ago

Your approach only replaces the dash with nothing, leaving the first set of digits in place. My approach removes all the beginning digits and the dash.

Tolsto Over a year ago

I used replace to remove the dash in order to to the integer typecast. I use the LIKE condition in the WHERE clause to effectively limit the MAX function to the part after the dash.

Tolsto Over a year ago

That's a fine solution to the problem. However, it should be noted that ORDER_BY and LIMIT is much more expensive than MAX on a unindexed column.

Marcin Zablocki · Accepted Answer · 2014-12-18 17:38:49Z

2

Use substring function with position function: http://www.postgresql.org/docs/8.1/static/functions-string.html to extract number after dash, and then use this value in MAX function as you have in your code now. You can also try to_number function.

It will look similiar to this:

MAX(substring(nr from position('-' in nr))::int)

answered Dec 18, 2014 at 17:38

Marcin Zablocki

10.8k1 gold badge40 silver badges52 bronze badges

4 Comments

Tolsto Over a year ago

I think I wasn't clear enough in my question. I need the dash and the number before the dash in the result (i.e. the actual data).

Marcin Zablocki Over a year ago

So use (position('-' in nr)-1)::int

Robbert Over a year ago

@Albert, you said "I would like to get the maximum value for nr taking into account only the part after the dash."

Tolsto Over a year ago

@Robbert Yes. However, I don't mean that in regard to the result but only in regard to the MAX function. Sorry, if I was unclear.

Collectives™ on Stack Overflow

SQL MAX function and strings

2 Answers 2

4 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related