Max number in a Varchar column!


Well, to find the maximum number in a VARCHAR2 Column is bit tricky and we can find it in many ways. An example can be to find the maximum number in the Segment1 column in mtl_system_items_b table. Here is one of the best ways to find it:

select max(to_number(segment1))
FROM mtl_system_items_b
where regexp_like(segment1, '^-?[[:digit:],.]*$');

2 Responses to Max number in a Varchar column!

  1. marcin says:

    Hi,
    can you explain the meaning of regexp_like(segment1, ‘^-?[[:digit:],.]*$’).

    • regexp_like(segment1, ‘^-?[[:digit:],.]*$’)

      To find the column values for ‘Segment1’ which contains ONLY digits.

      ^ Anchor the expression to the start of a line
      $ Anchor the expression to the end of a line
      [:digit:] Numeric digits
      ? Match 0 or 1 time
      * Match 0 or more times

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: