I have a string that reads “ORA_Sampl_34_20120530_123456.txt”. I am looking for an SQL query that can extract the value “34” from this string. How can I achieve that?
A practical approach to extracting “34” from the string is using the SUBSTRING_INDEX function. First, extract the substring before the third underscore using SUBSTRING_INDEX('ORA_Sampl_34_20120530_123456.txt', '_', 3), which results in “ORA_Sampl_34”. Then, use SUBSTRING_INDEX again to get the last segment after the second underscore: SUBSTRING_INDEX(SUBSTRING_INDEX('ORA_Sampl_34', '_', -1), '_', -1). This effectively isolates “34” as required.
You could try a regex approach if your SQL supports it. Using a pattern like [0-9]+ would capture sequences of digits, and you can extract “34” by selecting the third match. Just make sure your dbMS supports regex functions like REGEXP or REGEXP_SUBSTR for this to work.
Hey Jasper, ever consider using char_index to navigate through those underscores? Kinda curious how SUBSTRING coupled with that would work. Also, do you find these string manipulations often needed in your workflows or is this a one-off scenario? 
Hmm, jasper, I’m wondering, what about utilizing the LOCATE or POSITION functions in combination with SUBSTRING? Have you tried those? They might help pinpoint the part before and after “34” to extract it correctly. Do you also need to extract other parts of the string?