如何从字符串中获取特定值？

I have a column in having string value like

我有一个像字符串值一样的列

"BY *MAN MOBILE*0112305V7 - JAIPUR "

I need to get the the code written between * and - Example 0112305V7. Can someone help me for this issue

我需要在*和 - 例0112305V7之间编写代码。有人可以帮我解决这个问题

3 个解决方案

#1

Maybe something like this with regexp:

也许像regexp这样的东西:

select regexp_substr('BY *MAN MOBILE*0112305V7 - JAIPUR ','[[:digit:]]{3,}[[:alnum:]]{3,}') from dual;

#2

Try this (replace str with your column name):

试试这个(用你的列名替换str):

SELECT SUBSTR(str, INSTR(str, '*', 2) + 1, INSTR(str, '-') - INSTR(str, '*', 2) - 1)

Note: This will only work in the case your string consists of __*__*XXXX-____

注意:这仅适用于您的字符串由__ * __ * XXXX -____组成的情况

#3

Assuming that your strings always have two '*' and a single ' - ' after the '*' occurrences, to get the value between the second '*' and the ' - ', you can use :

假设你的字符串在'*'出现之后总是有两个'*'和一个' - ',要获得第二个'*'和' - '之间的值,你可以使用:

select substr(str, instr(str, '*', 1, 2)+1, instr(str, ' - ') - instr(str, '*', 1, 2) -1) as theNeededString,
       instr(str, '*', 1, 2) as theSecondStar,
       instr(str, ' - ') - instr(str, '*', 1, 2)-1 as lengthOfTheNeededString
from ( select 'BY *MAN MOBILE*0112305V7 - JAIPUR ' str from dual)

Which gives a quite self_explanatory result:

这给出了一个非常自我解释的结果:

THENEEDED THESECONDSTAR LENGTHOFTHENEEDEDSTRING
--------- ------------- -----------------------
0112305V7            15                       9

#1