Read Nth Word From Each Line C

Render Nth word and Nth character from a string

BeefStu Fellow member Posts: 310 Blue Ribbon

I accept a query that can get the  Nth discussion, second word (separated by spaces) '2'. How can I get the offset character of the Nth word  in my example I want the 'T' from the word 'Two' likewise to be returned

Select REGEXP_SUBSTR('one Two 3 four','\w+',one,ii) wordN  from dual  Expected output: Two T              

All-time Reply

• 

  Hi, @BeefStu

  Select REGEXP_SUBSTR('one Two 3 4','\w+',one,2) wordN  from dual                    

  Y'all're close! You said you want the words separated by spaces, but \w' will get words separated past other chracters (like commas) as well. '\S' (with a capital letter South) means whatsoever non-space character. To get the x-th grapheme, utilise SUBSTR.

  If table_x contains a cord column chosen str, you can get the north-thursday word and the x-th character of that word like this:

  WITH  got_wordn  AS ( 	SELECT REGEXP_SUBSTR ( str 			     , '\S+' 			     , one 			     , &word_num 			     ) AS wordn 	FROM	table_x ) SELECT  wordn ,	SUBSTR (wordn, &char_num, 1)	Every bit charx FROM	got_wordn ;                    

  Of course, you don't demand to use substitution variables like &word_num and &char_num. You tin can utilise any kind of expression in those places.

Answers

• 

  Hello, @BeefStu

  Select REGEXP_SUBSTR('one Two three four','\westward+',one,ii) wordN  from dual                  

  You're close! You said you want the words separated by spaces, just \w' volition get words separated past other chracters (like commas) too. '\S' (with a uppercase Southward) ways any non-space character. To get the 10-th character, use SUBSTR.

  If table_x contains a string column called str, you can get the due north-thursday word and the x-th grapheme of that discussion like this:

  WITH  got_wordn  Equally ( 	SELECT REGEXP_SUBSTR ( str 			     , '\Southward+' 			     , one 			     , &word_num 			     ) AS wordn 	FROM	table_x ) SELECT  wordn ,	SUBSTR (wordn, &char_num, one)	AS charx FROM	got_wordn ;                  

  Of course, y'all don't need to use substitution variables like &word_num and &char_num. You lot tin can employ any kind of expression in those places.

• 

  BeefStu Member Posts: 310 Blue Ribbon

  @Frank Kulash perfect thanks. To satisfy my curiosity could my original test CASE be expanded to include the substr too? For example, discover the Nth word and from that give-and-take notice the Nth character in i statement? I just couldn't get that too work then I posted the question. Thanks for your assistance and expertise its greatly appreciated

• 

  Hello, @BeefStu

  So, y'all want a single regular expression that returns a given character from a given word, is that right?

  Hither's one fashion:

  SELECT REGEXP_SUBSTR ( str 		     , '(\South+\south+){' || TO_CHAR (&word_num - 1) 		    		   || '}.{' 				   || TO_CHAR (&char_num - one) 				   || '}(.)' 		     , ane 		     , i 		     , Zippo 		     , 2 		     ) As charx FROM	table_x ;                  

  If &word_num = 1 and &char_num = two, then the 2d statement to REGEXP_SUBSTR is

                      '(\S+\s+){0}.{1}(.)'                  

• 

  mathguy Member Posts: 10,333 Blue Diamond

  The solution proposed by Mr. Kulash for this final request is in the correct general management, but it has one significant mistake. Later reading past the commencement n - 1 words (defined as non-empty substrings separated past whitespace), it starts counting characters regardless of whether they are "word" characters or whitespace. This causes incorrect results when the n'thursday word is shorter than k characters - the query will return the m'th character counting from the first graphic symbol of the north'th word, regardless of whether that m'th character is still in the same word. It may exist a whitespace grapheme, or a give-and-take character from a different give-and-take farther down (to the right) in the input string.

  This is easy to fix. Instead of "dot" use \S in all places. Or, if you lot really meant \westward for "word characters" (meaning that words may likewise exist delimited by dash, comma, etc. - not just by whitespace), yous can rewrite the whole affair using \due west and \W instead of \S and \s. Something similar this (using bind variables for n and m, instead of substitution variables):

  select str,        regexp_substr(str, '\w+', 1, :n) every bit wordN,        regexp_substr(str,          '^\West*(\w+\W+){' || to_char(:due north - i) || '}\due west{' || to_char(:grand - 1) || '}(\w)'          , i, ane, nada, 2)          equally wordN_charM from   (select '1 2 three 4' as str from dual);                  

  Note also that I anchored the regexp at the beginning of the input string (and so I had to permit for 0 or more non-give-and-take characters at the start, too). If n is larger than the number of words in the string, the regexp matching starting from the outset of the cord will fail. If we don't anchor at the kickoff of the string (with the ^ ballast), the regexp engine volition effort once again, attempting to match from the 2d graphic symbol, then from the third, ... - wasting a huge amount of time for no reason. The regexp engine doesn't utilize logic (like we do), to effigy out that the additional attempts will also automatically fail - it will simply blindly endeavour them all.

  For testing, try all combinations where m may be ane, or 2, or the word count, or GREATER THAN the discussion count, and n may be 1, or ii, or the length of the n'th discussion, or GREATER THAN the length of the due north'th give-and-take. Make sure you get the correct answer in all cases (including NULL when n is as well great or m is too swell).

bellindess.blogspot.com

Source: https://community.oracle.com/tech/developers/discussion/4490679/return-nth-word-and-nth-character-from-a-string

Share this post