CODEWITHSUNDEEP
sqlregexplsqloracle-sqldeveloper
mAtsea
mAtsea

Reputation: 67

REGEXP_LIKE ASC numbers

I have string 'KK12340000' and i want to validate it only if first 4 digits after KK are in ascending order so i use something like: 

select case when REGEXP_LIKE('KK12340000', '^KK(?=\d{4})(?:(.)\\1*|0?1?2?3?4?5?6?7?8?9?)\d{4}$') then 1 else 0 end as valid from dual;

But it returns 0. So is it even possible in oracle to validate numbers in ascending order?

Upvotes: 1

Views: 161

Answers (2)

not4fame
not4fame

Reputation: 21

You can try also something like this

SELECT 'KK12340000' LIKE 'KK%' AND 
SUBSTRING('KK12340000', 3, 1) - SUBSTRING('KK12340000', 4, 1) < 0 AND
SUBSTRING('KK12340000', 4, 1) - SUBSTRING('KK12340000', 5, 1) < 0 AND
SUBSTRING('KK12340000', 5, 1) - SUBSTRING('KK12340000', 6, 1) < 0

Upvotes: -1

XING
XING

Reputation: 9886

Try this:

  SELECT CASE
          WHEN REGEXP_LIKE (
                  'KK12230000',
                  '^KK(?:(.)\\1*|0?1?2?3?4?5?6?7?8?9?)\d{4}$')
          THEN
             1
          ELSE
             0
       END
          AS valid
  FROM DUAL;

Upvotes: 2

Related Questions