I have a alhpanumeric string. I also have one number with me. The string will always start with this number. How do I separate this number from the string and get the remaining part of the string?
e.g. string => 21fgggg21.lkkk and number=> 21
result=> fgggg21.lkkk
or
string=> 215699898.55fff and number=> 2
result=> 15699898.55fff
Any hint would be appreciated. Thanks.
substr(string, length(number)+1)
or
regexp_replace(string, '^'||number)
You could also use REGEXP_REPLACE. To remove '21' from the beginning of the string:
SELECT REGEXP_REPLACE('21fgggg21.lkkk', '^21') FROM DUAL;
REGEXP_REPLA
------------
fgggg21.lkkk
To remove '2' from the beginning of the string:
SELECT REGEXP_REPLACE('215699898.55fff', '^2') FROM DUAL;
REGEXP_REPLACE
--------------
15699898.55fff
By way of explanation...
^21 means "match 21 at the beginning of the string".REGEXP_REPLACE has an optional third parameter of what to replace the matched string with. Because you just want to remove the matched string you can omit the parameter, which replaces it with nothing.
If you are just looking to select it, you can use a combination of substr and instr.
substr(string, instr(string, 'number') + 1, len(string))
Your result should basically be the string started after where the number is located.
instr(string, 'number') would always be equal to 1 as string starts with number