Checking if values exist within string

Question

I have such string: 'Oracle Database Token: xxxx.';

I need to return true or false if xxxx is one of three variants:

1 or 11 or 111 or 1111 or 11111 or 111111 etc.
character '-'
number 123456789

I tried to build a regular expression for the regexp_substr() function but it turned out to be very hard.

Is this possible in a regular expression?

Please provide examples of what you want to identify and what you don't. And isn't the first and third the same thing? — Gordon Linoff
– Gordon Linoff, Commented Oct 14, 2019 at 15:19

MT0 · Accepted Answer · 2019-10-14 15:29:56Z

2

You can use TRANSLATE to test for the first condition and the other two are simple tests for equality:

SELECT *
FROM   your_table
WHERE  token = 'Oracle Database Token: -.'
OR     token = 'Oracle Database Token: 123456789.'
OR     (   token <> 'Oracle Database Token: .'
       AND TRANSLATE( token, '_1', '_' ) = 'Oracle Database Token: .' )

Sample Data:

CREATE TABLE your_table ( token ) AS
  SELECT 'Oracle Database Token: -.' FROM DUAL UNION ALL
  SELECT 'Oracle Database Token: 123456789.' FROM DUAL UNION ALL
  SELECT 'Oracle Database Token: 1.' FROM DUAL UNION ALL
  SELECT 'Oracle Database Token: 111.' FROM DUAL UNION ALL
  SELECT 'Oracle Database Token: 111111.' FROM DUAL UNION ALL
  SELECT 'Oracle Database Token: 12111.' FROM DUAL UNION ALL
  SELECT 'Oracle Database Token: -12.' FROM DUAL;

Query Output:

| TOKEN                             |
| :-------------------------------- |
| Oracle Database Token: -.         |
| Oracle Database Token: 123456789. |
| Oracle Database Token: 1.         |
| Oracle Database Token: 111.       |
| Oracle Database Token: 111111.    |

db<>fiddle here

You could also write it as a regular expression:

SELECT *
FROM   your_table
WHERE  REGEXP_LIKE( token, '^Oracle Database Token: (1+|123456789|-)\.$' )

But simple string functions/comparisons might be quicker.

edited Oct 14, 2019 at 15:29

answered Oct 14, 2019 at 15:22

MT0

173k12 gold badges70 silver badges136 bronze badges

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

2 Comments

geekcode Over a year ago

Can you help me with convert to REGEXP_SUBSTR? select REGEXP_substr( 'Oracle Database Token: 123456789.', '^Oracle Database Token: (1+|123456789|-)\.$', 1, 1, null, 1) from dual not working

MT0 Over a year ago

@geekcode You want SUBSTR( token, 24, LENGTH( token ) - 24 ) or REGEXP_SUBSTR( token, '^Oracle Database Token: (1+|123456789|-)\.$', 1, 1, NULL, 1 ) for example db<>fiddle

Gordon Linoff · Accepted Answer · 2019-10-14 15:42:32Z

0

I think you want:

where regexp_like(suchstring, '^Oracle Database Token: ([0-9]+|-)[.]'

EDIT:

If you want those specific numbers:

where regexp_like(suchstring, '^Oracle Database Token: (1+|-|123456789)[.]'

edited Oct 14, 2019 at 15:42

answered Oct 14, 2019 at 15:20

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

MT0 Over a year ago

This will match tokens like 123 and not just 123456789 or repeated 1s.

Gordon Linoff Over a year ago

@MT0 . . . I interpreted the third item as just any numbers. I guess you're right that it refers to that one particular number.

Collectives™ on Stack Overflow

Checking if values exist within string

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related