CODEWITHSUNDEEP
sqloracle-databaseregexp-substr
cardonas
cardonas

Reputation: 109

How to extract a specific string from a column

I am trying to extract a specific string that starts with 'A0' and give me back the string A0 along with the rest of 9 values from a column in a table . I need to extract an auth number in a claims notes column in a table.

I am trying to use the regexp_substr function. I want to extract any string that starts with 'A0' in the clm_notes column in the table .

SELECT 
regexp_substr(CLM_NOTES, '[^A0]+', 1,1) as auth_num
FROM claims_table

this is how the column looks in the table

Clm_notes column 
New Auth from auth - A071869573

The Desired results I want is to extract the string that starts with 'A0'

A071869573

Upvotes: 0

Views: 368

Answers (2)

Bohemian
Bohemian

Reputation: 425418

Rather than match what you want, match what you don't want and delete it:

select
    regexp_replace(CLM_NOTES, '.*- A0', 'A0') as auth_num
from claims_table

This matches everything before and including '- A0' and replaces it with just 'A0', effectively deleting everything preceding 'A0'.

Upvotes: 0

Radagast81
Radagast81

Reputation: 3016

You will need something like the following:

SELECT 
regexp_substr(CLM_NOTES, 'A0.*$', 1,1) as auth_num
FROM claims_table

Or perhaps if all characters after A0 are digits:

SELECT 
regexp_substr(CLM_NOTES, 'A0[0-9]*$', 1,1) as auth_num
FROM claims_table

Another faster way may be the following:

SELECT 
substr(CLM_NOTES, instr(CLM_NOTES,'A0')) as auth_num
FROM claims_table

Upvotes: 1

Related Questions