Search substrings in string and divide

check if a string is a substring of another string in java
how to divide a string into substrings in c++
check if a string is a substring of another string in c
program to find substring in a string in c++
how to split a string in java with delimiter
string split c#
substr c++
how to divide a string into substrings in java

I have a table with varchar2(5000 char) column that contains several sentences. Somewhere in such a sentence an number is shown, followed by another number. I want to select those numbers and divide them. Not sure how to do this in sqlplus oracle 11g.

I think I should use functions like SUBSTR, REGEXP_SUBSTR, INSTR. Not sure which is best suitable for this job.

columns are like this:

blah bahwl balwo hxkswl blahxhsh alshbhe NUM 40003.26 in 4 pieces. etc
bwh bahwl bafado hxkswl alshbhe NUM 6006.16 in 9 pieces. etc
badh baadfl balwo hxkswl blahxhsh alshbhe NUM 200 in 30 pieces. etc
bfda bdafl hxkswl NUM 33 in 4 pieces. etc
blcfh bfdwl bfdlwo alshbhe NUM 54545.01 in 700 pieces. etc

So I want to search and select the number directly after NUM and select the number between "in" and "pieces" and then divide those numbers.

select .... 40003.26 / 4 
select .... 6006,16 / 9 
select ....  200 / 30 

etc.

Any help kindly appreciated

You can use regexp_substr, regexp_replace with the contribution of l/rtrim as below

with t1 as
 (select 'blah bahwl balwo hxkswl blahxhsh alshbhe NUM 40003.26 in 4 pieces. etc
             bwh bahwl bafado hxkswl alshbhe NUM 6006.16 in 9 pieces. etc
             badh baadfl balwo hxkswl blahxhsh alshbhe NUM 200 in 30 pieces. etc
             bfda bdafl hxkswl NUM 33 in 4 pieces. etc
             blcfh bfdwl bfdlwo alshbhe NUM 54545.01 in 700 pieces. etc' as str
    from dual),
t2 as
 (select regexp_substr(str, '(.*)pieces.*', 1, level) as str
    from t1
   cross join dual
  connect by level <= regexp_count(str, 'pieces')),
t3 as
 (select str,
         regexp_replace(str, '[^0-9]in[^0-9]', '/') str1,
         regexp_substr(str, '[^0-9]*') str2,
         regexp_substr(str, '[^0-9]*$') str3
    from t2)
select rtrim(ltrim(str1, str2), str3) as "Result String" from t3


Result String
-------------
40003.26/4
6006.16/9
200/30
33/4
54545.01/700

Demo

Check if a string is substring of another, Given two strings s1 and s2, find if s1 is a substring of s2. If yes For current index i, check for. pattern match */. for (j = 0; j < M; j++). if (s2[i + j] != s1[j]). break ;. Divides a single string into two substrings. You can divide the string at a certain character or substring. Search/Split String searches string for the string or character in search string/char. The function splits the string and returns the resulting two strings in substring before match and match + rest of string. Details

This option selects NUM blabla parts of the input string, as many times as NUM appears in there (that's what INTER CTE does). The final SELECT (lines 13 onward) extracts numeric values and divides them to produce the result.

SQL> with test (col) as
  2    (select 'blah bahwl balwo hxkswl blahxhsh alshbhe NUM 40003.26 in 4 pieces. etc
  3  bwh bahwl bafado hxkswl alshbhe NUM 6006.16 in 9 pieces. etc
  4  badh baadfl balwo hxkswl blahxhsh alshbhe NUM 200 in 30 pieces. etc
  5  bfda bdafl hxkswl NUM 33 in 4 pieces. etc
  6  blcfh bfdwl bfdlwo alshbhe NUM 54545.01 in 700 pieces. etc' from dual
  7    ),
  8  inter as
  9    (select regexp_substr(col, 'NUM \d+(\.\d+)? in \d+', 1, level) res
 10     from test
 11     connect by level <= regexp_count(col, 'NUM')
 12    )
 13  select res,
 14         regexp_substr(res, '\d+(\.\d+)?') c1,
 15         regexp_substr(res, '\d+$') c2,
 16         --
 17         round(to_number(regexp_substr(res, '\d+(\.\d+)?'), '99999999D99',
 18                         ' NLS_NUMERIC_CHARACTERS = ''.,''') /
 19               to_number(regexp_substr(res, '\d+$')), 2) result
 20  from inter;

RES                  C1         C2             RESULT
-------------------- ---------- ---------- ----------
NUM 40003.26 in 4    40003.26   4            10000,82
NUM 6006.16 in 9     6006.16    9              667,35
NUM 200 in 30        200        30               6,67
NUM 33 in 4          33         4                8,25
NUM 54545.01 in 700  54545.01   700             77,92

SQL>

Search substrings in string and divide, You can use regexp_substr , regexp_replace with the contribution of l / rtrim as below with t1 as (select 'blah bahwl balwo hxkswl blahxhsh� Check if a string can be split into even length palindromic substrings; Split the string into substrings using delimiter; Find the starting indices of the substrings in string (S) which is made by concatenating all words from a list(L) Split the binary string into substrings with equal number of 0s and 1s

If you can guarantee that ALL (and I do mean ALL) rows will have a fragment like the one you described: NUM ..... in ..... pieces where ..... are two numbers with the second number not 0 (so we don't divide by 0), then you could use XMLQuery for this, like so:

with
  test_data as (
    select 1 id, 'blah bahwl NUM 40003.26 in 4 pieces. etc' str from dual union all
    select 2   , 'bwh balshbh NUM 6006.16 in 9 pieces. etc'     from dual union all
    select 3   , 'badh sh alshbh NUM 200 in 30 pieces. etc'     from dual union all
    select 4   , 'bfda bdafl hxksw NUM 33 in 4 pieces. etc'     from dual union all
    select 5   , 'bl lshbh NUM 54545.01 in 700 pieces. etc'     from dual
  )
select id,
       xmlquery(regexp_replace(str, '^.*?NUM(.*?)in(.*?)pieces.*$', '\1 div \2')
                returning content).getNumberVal() as division_result
from   test_data
;    

   ID DIVISION_RESULT
----- ---------------
    1    10000.815000
    2      667.351111
    3        6.666667
    4        8.250000
    5       77.921443

Even if you are not familiar with XMLQuery, it shouldn't be hard to understand how it works in this special case. You may want to SELECT the result of REGEXP_REPLACE separately, to see the actual input to XMLQuery.

However, this solution will choke on any of the additional inputs in the bigger dataset shown below as a WITH clause.

If such exceptions are possible, you must tell us how each should be handled. Notice that id = 6 doesn't have the required fragment at all; id = 7 is missing the second number; id = 8 has the second number equal to 0; and id = 9 has text where there should be a number.

This shows why, perhaps, if you have to deal with such issues, you should consider adding two columns, possibly virtual ones if your Oracle version is high enough, to hold just the needed numbers - then handling possible exceptions would become much easier. This would also, obviously, get your data model closer to First Normal Form, which your current model violates flagrantly.

with
  test_data as (
    select 1 id, 'blah bahwl NUM 40003.26 in 4 pieces. etc' str from dual union all
    select 2   , 'bwh balshbh NUM 6006.16 in 9 pieces. etc'     from dual union all
    select 3   , 'badh sh alshbh NUM 200 in 30 pieces. etc'     from dual union all
    select 4   , 'bfda bdafl hxksw NUM 33 in 4 pieces. etc'     from dual union all
    select 5   , 'bl lshbh NUM 54545.01 in 700 pieces. etc'     from dual union all
    select 6   , 'blah blah blah'                               from dual union all
    select 7   , 'blah blah NUM 23 in pieces etc.'              from dual union all
    select 8   , 'blah NUM 23 in 0 pieces'                      from dual union all
    select 9   , 'blah NUM 12 in three pieces etc.'             from dual
  )
    ............  How should the exceptions for id between 6 and 9 be handled?

Get string between two strings in a string, Length; int pTo = St.LastIndexOf(" - "); String result = St.Substring(pFrom, pTo on the search</returns> public static string Substring(this string @this, string from Split with the overload that takes a string[] for the delimiters but that would also � Besides the split() method Strings can also be split using a StringTokenizer. StringTokenizer is even more restrictive than String.split(), and also a bit harder to use. It is essentially designed for pulling out tokens delimited by a fixed set of characters (given as a String). Each character will act as a separator.

Search/Split String Function - LabVIEW 2018 Help, Divides a single string into two substrings. You can divide the string at a certain character or substring. Search/Split String searches string for the string or character in search string/char. The function splits the string and returns the resulting two strings in substring before match and match + rest of string. Java String substring() The java string substring() method returns a part of the string. We pass begin index and end index number position in the java substring method where start index is inclusive and end index is exclusive. In other words, start index starts from 0 whereas end index starts from 1.

Manipulating Characters in a String (The Java™ Tutorials , String[] split(String regex, int limit), Searches for a match as specified by the string Here are some other String methods for finding characters or substrings� Split the binary string into substrings with equal number of 0s and 1s; Check if a string can be split into even length palindromic substrings; Split a given string into substrings of length K with equal sum of ASCII values; Remove minimum characters from string to split it into three substrings under given constraints

Search Within A String - How to play with strings in C, Let's see how searching works. Search for a character in a string - strchr & strrchr. The strchr function returns the first occurrence of a character� I want to get two string values separated by a special character. For example, say user types in a search query like japanese->chinese (or with spaces japanese ->; chinese).

Comments
  • Is it GUARANTEED that such numbers will exist in EVERY row? That is, there will ALWAYS be a fragment of the form NUM .... in ..... pieces in every single row in the table? And the ..... will always be numbers, and the second number will always be different from 0 (so that you don't divide by 0)? Can you assume that this will ALWAYS be the case, or does your query have to handle the possibility of such exceptions?
  • Yes it is guaranteed that these numbers exist in every row and the .... will always be numbers (with decimals) and the second number is always >0 and a round number. The query does not need to handle exceptions.
  • Thanks for your answer. I will try it shortly.
  • Thanks you for your answer. I will try it shortly.
  • Just being curious: did you try any of proposed solutions? Does any of them work as you'd want it to?
  • thank you for your answer. I will look into it shortly.