Omkar Omkar - 2 months ago 6
SQL Question

Find a SUBSTRING with Oracle regexp that has an exact length

I'm trying to fetch substring from a column of Oracle table.

For E.g.
My data is
"Thanks for registering with us, your registered no.ABC34252 Confirm your availability."
Now i want a query that will retrieve ABC34252 . Size of this registered no is fixed, but the problem is the complete text message varies in different cases.

Please Help

Answer

This will work irrespective of the size of the complete test message.It will pick the alphanumeric characters.

case 1: String lenght is ----

select regexp_substr('Thanksing wi no.ABC34252 Confirm your availability.','[[:alpha:]]+[[:digit:]]+' )  Output,
       length('Thanksing wi no.ABC34252 Confirm your availability.')  Stringlen
from dual;

case 2: String lenght differs ----

select regexp_substr('Thanksingakdakjdshsak djsahdjsahdk sa o.ABC34252 Confirm your availability.','[[:alpha:]]+[[:digit:]]+' )  Output,
        length('Thanksingakdakjdshsak djsahdjsahdk sa o.ABC34252 Confirm your availability.') Stringlen
from dual;
Comments