How can I search for occurences in a string with Oracle REGEXP_INSTR

Question

I have a string like A12345678B

I want to be able to check if a string starts with a character, is followed by 8 digits and ends with a character.

We are trying:

SELECT REGEXP_INSTR('A12345678B', '[[:alpha:]]{1}[[:digit:]]{8}[[:alpha:]]{10}',1,1,1,'i') from DUAL

This returns :

We want to be able to determine that if a string does NOT start and end with a character and is not followed by 8 digits after the first character ( see sample string above ), THEN this is not the string that we are looking for .

Avinash Raj · Accepted Answer · 2014-10-20 10:23:15Z

4

string starts with a character

^[[:alpha:]]

is followed by 8 digits

[[:digit:]]{8}

ends with a character

[[:alpha:]]$

So the complete regex would be,

^[[:alpha:]][[:digit:]]{8}[[:alpha:]]$

This [[:alpha:]]{10} in your regex assumes that there must be exactly 10 alphabets present after to the 8 digit number.

edited Oct 20, 2014 at 10:23

answered Oct 20, 2014 at 10:15

Avinash Raj

175k32 gold badges247 silver badges289 bronze badges

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

Comments

vks · Accepted Answer · 2014-10-20 10:17:06Z

1

^[a-zA-Z][0-9]{8}[a-zA-Z]$

Try this.Put the anchors.

answered Oct 20, 2014 at 10:17

vks

68.1k11 gold badges96 silver badges132 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2014-10-20 10:19:57Z

1

Try using regexp_like if you want a full string match. For a match, use:

WHERE REGEXP_LIKE('A12345678B', '^[[:alpha:]]{1}[[:digit:]]{8}[[:alpha:]]{1}$')

For a non-match use:

WHERE NOT REGEXP_LIKE('A12345678B', '^[[:alpha:]]{1}[[:digit:]]{8}[[:alpha:]]{1}$')

Note: this assumes that you want to filter the results (which is what your question implies, not put a flag into the select clause.

answered Oct 20, 2014 at 10:19

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

How can I search for occurences in a string with Oracle REGEXP_INSTR

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related