tag:blogger.com,1999:blog-8677649049588007585.post1756968455203795590..comments2023-06-18T16:15:22.432+01:00Comments on PL/SQL Challenge: Finding Holes in Algorithms - "Number in String" (5979)Steven Feuersteinhttp://www.blogger.com/profile/16619706770920320550noreply@blogger.comBlogger7125tag:blogger.com,1999:blog-8677649049588007585.post-9721865721361952692011-08-19T19:36:06.186+01:002011-08-19T19:36:06.186+01:00Thanks to you Iudith for enhancing my function.
Ni...Thanks to you Iudith for enhancing my function.<br />NielsAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-31356379487776905322011-08-19T07:19:35.977+01:002011-08-19T07:19:35.977+01:00Yes, indeed a good lesson learned. Yesterday and t...Yes, indeed a good lesson learned. Yesterday and today the first thing I did was check the minimal version.Wim de Langehttps://www.blogger.com/profile/05505341375827859005noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-58050737372843862682011-08-18T22:22:24.618+01:002011-08-18T22:22:24.618+01:00Hello Kim,
The interesting is that the PDF version...Hello Kim,<br />The interesting is that the PDF version <br />of the SQL Language reference downloadable from the same 11.1 page that you indicated does contain REGEXP_COUNT :) :)<br /><br />The "gold dream" ?<br />To use the documentation all the time, except while answering the quizzes :) :) :)<br /><br />Best Regards,<br />Iudithiudithhttps://www.blogger.com/profile/04905902445036068357noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-44465864583878904152011-08-18T09:18:58.209+01:002011-08-18T09:18:58.209+01:00Agree, Steven - I ought to know by now that I cann...Agree, Steven - I ought to know by now that I cannot just skip the headers and dive right into the question :-)<br /><br />I did notice "version 11" and tried to be alert. But I failed on REGEXP_COUNT anyway because 11.1 documentation was not as up-to-date as 11.2 documentation. This page (http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/functions001.htm#i88893) does not mention REGEXP_COUNT, whereas this one does (http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/functions002.htm). My lesson learned: Go for the newest docs :-)Kim Berg Hansenhttps://www.blogger.com/profile/06491635470794828550noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-42230663752808752692011-08-17T15:34:11.572+01:002011-08-17T15:34:11.572+01:00Thanks, Iudith. I will use your modified version i...Thanks, Iudith. I will use your modified version in the answer text.<br /><br />Wim - I am reluctant to highlight the version and/or other aspects of the quiz. We present all the information on the page. It seems like when a person takes the quiz, they would start at the top, read all the information, then dive into the details of the quiz. If players hurriedly skip over information on the page...well...that's a good lesson learned, isn't it?Steven Feuersteinhttps://www.blogger.com/profile/16619706770920320550noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-52039254647148062252011-08-17T13:25:53.879+01:002011-08-17T13:25:53.879+01:00Hello All,
Maybe what was missing from the quiz te...Hello All,<br />Maybe what was missing from the quiz text was the clear specification that occurrences that are counted should be always supposed as non-overlapping.<br /><br />In fact, this is (more or less "silently") the basic assumption of how all the Oracle built-ins work, for example REPLACE, REGEXP_COUNT and INSTR (the 4-th parameter).<br /><br />Therefore, the 100% complete solution would be to add a 3-rd BOOLEAN parameter to the function,<br />with TRUE=allow overlaps, FALSE=do not allow overlaps and make the code count accordingly.<br /><br />Adapting the above solution:<br /><br />CREATE OR REPLACE<br />FUNCTION InStrCount (pString IN VARCHAR2,<br /> pSearch IN VARCHAR2,<br /> pAllowOverlaps IN BOOLEAN DEFAULT FALSE)<br /> RETURN BINARY_INTEGER<br />/*====================================================================<br /> Returns the number of occurrences of a search-string in a string<br /> --------------------------------------------------------------------<br /> pString: String to search for the occurrences of "pSearch"<br /> pSearch: String to search in "pString"<br /> --------------------------------------------------------------------<br /> Result: number of occurrences of "pSearch" in "pString"<br /> --------------------------------------------------------------------<br /> History: 000-00 nhecker<br /> 000-01 iudith<br /> ====================================================================*/<br />IS<br /><br /> Result BINARY_INTEGER := 0;<br /> iPos BINARY_INTEGER;<br /> iStart BINARY_INTEGER := 1;<br /><br />BEGIN<br /> LOOP<br /> iPos := InStr( pString, pSearch, iStart);<br /> EXIT WHEN (iPos = 0);<br /><br /> Result := Result + 1;<br /> -- iStart := iPos + 1;<br /> iStart := iPos + <br /> CASE WHEN pAllowOverlaps THEN 1 <br /> ELSE LENGTH(pSearch)<br /> END;<br /> END LOOP;<br /> RETURN (Result);<br />END InStrCount;<br />/<br /><br />This way everybody will be satisfied :) :)<br /><br />Thanks & Best Regards,<br />Iudithiudithhttps://www.blogger.com/profile/04905902445036068357noreply@blogger.comtag:blogger.com,1999:blog-8677649049588007585.post-40065512532414461832011-08-17T07:28:06.406+01:002011-08-17T07:28:06.406+01:00Beside problems how many times is a substring in a...Beside problems how many times is a substring in a string (some characters are counted twice, is it really another different substring?) is it an idea to make it more clear that the Oracle version is not 10 as is the default? I did not notice, so I missed an answer (reg_count) as I thought, that it was not existing. If I had noticed the minimum version I had started google on this one. Bold, red comes to mind.Wim de Langehttps://www.blogger.com/profile/05505341375827859005noreply@blogger.com