1 2 Previous Next 20 Replies Latest reply on Mar 30, 2012 11:44 AM by 919157

    How to split the alphanumeric String without using any function.

    919157
      Hi All,

      I wrote one procedure in oracle to split the alphanumeric string, but in that I am using one oracle function substr() for getting the character. Its working fine but I don't want to use that function and I want to optimize this procedure.

      The procedure which I wrote is as follow:-
      create or replace procedure readColumnNameFromTheString(input_kpiformula IN varchar2) as
           total_length number := 0;
           char_value char2(1);
           char_ascii_value number;
           subString_value varchar2(10);
           input_string varchar2(1000) := input_kpiformula;
      Begin
                
           total_length := length(input_string);
           for i in 1 .. total_length loop
                char_value := substr(input_string, i, 1);  -- Here I don't want to use this function, there is any better way to do this.
                char_ascii_value := ascii(char_value);
                
                IF char_ascii_value >= 48 and char_ascii_value <= 57 or char_ascii_value >= 65 and 
                                   char_ascii_value <= 90 or char_ascii_value >= 97 and char_ascii_value <=122 Then
                
                     subString_value := subString_value||char_value;
                else
                     dbms_output.put_line('The value is: '||subString_value);
                     subString_value := '';
                end if;
           end loop;
           dbms_output.put_line('The value is: '||subString_value);          
           
      End;
      There is any better way to do this?

      Thanks in advance for your help...
        • 1. Re: How to split the alphanumeric String without using any function.
          Centinul
          It seems odd that you would not want to use a provided and supported Oracle function. I would imagine there is a more efficient way to do what you want, but unfortunately I don't know exactly what you are trying to accomplish. Please see: {message:id=9360002}.

          Basically, we need:

          1. Oracle version
          2. Sample data in the form of CREATE / INSERT statements
          3. Expected output
          4. Business rules

          to be able to help you.
          • 2. Re: How to split the alphanumeric String without using any function.
            Peter vd Zwan
            Hi,

            While you're at it, you should also take out the length and the ascii functions.
            create or replace procedure readColumnNameFromTheString(input_kpiformula IN varchar2) as
                 total_length number := 0;
                 char_value char2(1);
                 char_ascii_value number;
                 subString_value varchar2(10);
                 input_string varchar2(1000) := input_kpiformula;
            Begin
                      
                 total_length := length(input_string);   --length is a function
                 for i in 1 .. total_length loop
                      char_value := substr(input_string, i, 1);  -- Here I don't want to use this function, there is any better way to do this.
                      char_ascii_value := ascii(char_value);        --ascii is a function
                      
                      IF char_ascii_value >= 48 and char_ascii_value <= 57 or char_ascii_value >= 65 and 
                                         char_ascii_value <= 90 or char_ascii_value >= 97 and char_ascii_value <=122 Then
                      
                           subString_value := subString_value||char_value;
                      else
                           dbms_output.put_line('The value is: '||subString_value);
                           subString_value := '';
                      end if;
                 end loop;
                 dbms_output.put_line('The value is: '||subString_value);          
                 
            End;
            Regards,
            Peter
            • 3. Re: How to split the alphanumeric String without using any function.
              919157
              Hi Centinul,

              First thanx for your quick response.

              I don't want the supported function, when we will use oracle supported function in our procedure it will take little extra time because we are passing whole string in substr() function inside the while loop. So passing the parameter and returning to the same function is taking fraction of seconds.

              Now let suppose that at the same time we have 1 lakhs records which we have to parse, then it will consumed minimum 1-2 minutes, I just want to save that time.

              I hope now your doubt is clear, "why I don't want the *substr()*".

              I am incorporating all the point which you mentioned in your last post

              1) My oracle version is "10.2.0.4.0"
              2) Actually we will pass the complex formula and we don't know how much long formula enter by the client.
              Ex: col1+((col2*col42)/(col32+col43))+col132*col198-col3 and so on....

              We have to take these colname and against of that we have to give them result.

              3) In expected output, we will get columnName which is mentioned in the above formula. Which is working fine by my code.

              4) we have to take only columnName which is alphanumeric.

              Again thanx in advance for your help and guidance.
              • 4. Re: How to split the alphanumeric String without using any function.
                AlanWms
                If you are after efficiency then you might want to try just using one function call to do the whole thing
                with data as (
                
                select '$123*&^abc..;ABC' mycol from dual
                
                )
                select mycol, regexp_replace(mycol,'[^[:alpha:][:digit:]]','') stripped from data;
                Result:
                MYCOL                     STRIPPED
                $123*&^abc..;ABC     123abcABC
                Edited by: AlanWms on Mar 28, 2012 8:34 AM - added result of query
                • 5. Re: How to split the alphanumeric String without using any function.
                  919157
                  Hi peter,

                  Thanks for your promptly response...

                  I have use the length function outside of while loop, its means it will execute only once while this procedure is called.

                  ASCII function I took because I don't know how I will take character ascii value.

                  I just want any alternate way which should be best then the procedure which I wrote.

                  Please help me on this matter

                  Thanks in advance for your help and suggestion.

                  Hem
                  • 6. Re: How to split the alphanumeric String without using any function.
                    919157
                    Hi Alan,

                    Thanks for your promptly response...

                    I didn't get this..
                    with data as (
                     
                    select '$123*&^abc..;ABC' mycol from dual
                     
                    )
                    select mycol, regexp_replace(mycol,'[^[:alpha:][:digit:]]','') stripped from data;
                    "with data as" is a function?. With that we want to get the result in new line but according to your code we are replacing all the special character and concatenating all the values.

                    If I am wrong then clarify me? I want to know this.

                    Thanks in advance for your help and suggestion...
                    • 7. Re: How to split the alphanumeric String without using any function.
                      chris227
                      regexp is effecient? in what sense? not in cpu cycles i guess ...

                      however, if proposed there is a shortcut [:alnum:].
                      • 8. Re: How to split the alphanumeric String without using any function.
                        Peter vd Zwan
                        Hi,
                        create or replace
                        procedure readColumnNameFromTheString(input_kpiformula IN varchar2) as
                             total_length number := 0;
                             char_value char(1);                    --not char2 but char
                             char_ascii_value number;
                             subString_value varchar2(10);
                             input_string varchar2(1000) := input_kpiformula;
                        Begin
                                  
                             total_length := length(input_string);   --length is a function
                             for i in 1 .. total_length loop
                                  char_value := substr(input_string, i, 1);  -- Here I don't want to use this function, there is any better way to do this.
                                  char_ascii_value := ascii(char_value);        --ascii is a function
                                  
                                  IF char_ascii_value >= 48 and char_ascii_value <= 57 or char_ascii_value >= 65 and 
                                                     char_ascii_value <= 90 or char_ascii_value >= 97 and char_ascii_value <=122 Then
                                  
                                       subString_value := subString_value||char_value;
                                  else
                                       dbms_output.put_line('The value is: '||subString_value);
                                       subString_value := '';
                                  end if;
                             end loop;
                             dbms_output.put_line('The value is: '||subString_value);          
                             
                        End;
                        I don't want the supported function, when we will use oracle supported function in our procedure it will take little extra time because we are passing whole string in substr() function inside the while loop. So passing the parameter and returning to the same function is taking fraction of seconds.

                        Now let suppose that at the same time we have 1 lakhs records which we have to parse, then it will consumed minimum 1-2 minutes, I just want to save that time.

                        >

                        And what about the overhead for running the loop and the whole procedure in the first place.

                        Look into the reg expresions as shown by AlanWms

                        Also.
                        When I run your procedure with input:
                        'col1+((col2*col42)/(col32+col43))+col132*col198-col3'

                        the result is:
                        The value is: col1
                        The value is: 
                        The value is: 
                        The value is: col2
                        The value is: col42
                        The value is: 
                        The value is: 
                        The value is: col32
                        The value is: col43
                        The value is: 
                        The value is: 
                        The value is: col132
                        The value is: col198
                        The value is: col3
                        The value is: 
                        but if I use it with:
                        'peter.col1+((john.col2*scott.col42)/(substr(col32,1,2)+col_43))+col132*col198-pck_x.function_y(inputstr => col3) '
                        I get:
                        The value is: peter
                        The value is: col1
                        The value is: 
                        The value is: 
                        The value is: john
                        The value is: col2
                        The value is: scott
                        The value is: col42
                        The value is: 
                        The value is: 
                        The value is: substr
                        The value is: col32
                        The value is: 1
                        The value is: 2
                        The value is: 
                        The value is: col
                        The value is: 43
                        The value is: 
                        The value is: 
                        The value is: col132
                        The value is: col198
                        The value is: pck
                        The value is: x
                        The value is: function
                        The value is: y
                        The value is: inputstr
                        The value is: 
                        The value is: 
                        The value is: 
                        The value is: col3
                        The value is: 
                        The value is: 
                        These are not all columns (peter, function, x, y, scott, pck, john, 1, ....)
                        and not all collumns are there (col_43)

                        So you may have a a look at this aproach. I think it does not work.

                        If you explain why you need this maybe we can better help you.

                        Regards,
                        Peter
                        • 9. Re: How to split the alphanumeric String without using any function.
                          AlanWms
                          I would think regexp would be more efficient than calling a user written function and looping thru the characters one at a time, you can do it without regexp (see stripped2 below):
                          with data as (
                          
                          select '$123*&^abc..;ABC' mycol from dual
                          
                          )
                          select mycol, regexp_replace(mycol,'[^[:alpha:][:digit:]]','') stripped,
                                 translate(mycol,':' || translate(lower(mycol),':abcdefghijklmnopqrstuvwxyz0123456789',':'),':') stripped2 from data;
                          MYCOL                     STRIPPED        STRIPPED2
                          $123*&^abc..;ABC     123abcABC        123abcABC
                          • 10. Re: How to split the alphanumeric String without using any function.
                            919157
                            I Just want to traverse the string, character by character without using substr().

                            Is there any efficient way to do this?

                            I hope now you all got my query?

                            Thanks in advance for your help and understanding me.
                            • 11. Re: How to split the alphanumeric String without using any function.
                              chris227
                              hi,

                              that's what i was thinking of: translate, but i didn't get the trick yet with the translate in the translate. Nice one, i would give you the points for that if i could ;-)

                              so at last we have to get rid of the colon too
                              with data as (
                               select '$123*&^ab:c..;ABC' mycol from dual
                               )
                              select
                              replace(translate(
                              mycol
                              ,':' ||
                                  translate(
                                      (mycol)
                                      ,':ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'
                                      ,':')
                              ,':'),':','')
                              from data
                              regards
                              • 12. Re: How to split the alphanumeric String without using any function.
                                AlanWms
                                I thought you were just trying to strip out the non-alpha characters, now that I see the sample output I see that you are also splitting the string at these places.

                                WITH alias AS (SELECT statement)

                                Is just a way to generate some sample data in this case, it is Oracle syntax.

                                My code just strips the special characters but does not split the line. I think it should be possible to split the line where you want to using CONNECT BY LEVEL, but it is a little more work than I have time to put into it right now.

                                Edited by: AlanWms on Mar 28, 2012 9:18 AM - typo
                                • 13. Re: How to split the alphanumeric String without using any function.
                                  AlanWms
                                  Right, I should have had a colon in my test case.
                                  • 14. Re: How to split the alphanumeric String without using any function.
                                    Dom Brooks
                                    I hope now you all got my query?
                                    The question itself was clear from the outset.
                                    When you don't get the answers you expect, sometimes it can be an indication that you yourself are not thinking along the right lines.

                                    There are alternatives to substr but are they likely to be more efficient?
                                    Is it likely that a call to the SQL engine is going to be more efficient?

                                    Over time, if there have been more efficient ways of getting a single character from a string, it is possible that the internal implementation of subtr will have changed to accomodate the efficiency.

                                    Is there any efficient way to do this?
                                    What makes you think substr is not efficient?
                                    Have you run your code through the plsql compiler and have evidence that substr is a major bottleneck?
                                    1 2 Previous Next