1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Need help on sql - substr and instr

Discussion in 'SQL PL/SQL' started by Swapna Sree Nomula, May 27, 2016.

  1. Swapna Sree Nomula

    Swapna Sree Nomula Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Hyderabad
    Hi friends,

    I have the following requirement -

    SELECT SUBSTR('99886 NEW-PROJ', 0, INSTR('99886 NEW-PROJ', ' ')-1) FROM DUAL;
    select SUBSTR('99887-NEW PROJ', 0, INSTR('99887-NEW PROJ', '-')-1) from dual;

    Individually both the above sql Queries are working but i need to display only the number by parsing wither space or '-'.

    How to build a sql query to display only the number in both the trings.

    Thanks In Advance!

    Swapna Sree Nomula
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You don't start at position 0, you start at position 1:

    SELECT SUBSTR('99886 NEW-PROJ', 1, INSTR('99886 NEW-PROJ', ' ')-1) FROM DUAL;
    select SUBSTR('99887-NEW PROJ', 1, INSTR('99887-NEW PROJ', '-')-1) from dual;

    These should give you what you expect to see.
     
  3. Swapna Sree Nomula

    Swapna Sree Nomula Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Hyderabad
    Thanks Zargon, i could eliminate the space by starting at position 1.

    I would like to have a single sql query which will handle both the following strings and result with starting number.
    Example -
    '99887 NEW-PROJ'
    99887-NEW PROJ

    Sql query should display only the starting number- 99887 and parse the space or charcater '_' along with other information from the string.

    Thanks In Advance!

    Swapna Sree Nomula
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    it is possible to use regular expressions.

    Code (SQL):
    SELECT regexp_substr('99886 NEW-PROJ','\d+',1,1) FROM dual;
     
  5. Swapna Sree Nomula

    Swapna Sree Nomula Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Hyderabad
    Thanks krasnoslobodtsev_si!

    I am not getting the correct result for the following string, when i have numbers and characters.
    Eg:
    9CS02 - DARPA C-SCAN II

    SELECT regexp_substr('9CS02 - DARPA C-SCAN II','\d+',1,1) FROM dual;

    I am getting the result as '9'

    Thanks In Advance!

    Swapna Sree Nomula
     
  6. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Your example :"99886 NEW-PROJ"
    The query has returned the first figure (or group of digits), as it was supposed.

    It "9CS02 - DARPA C-SCAN II " is other template.

    What result is necessary?
    The result is necessary as 902?
     
  7. Swapna Sree Nomula

    Swapna Sree Nomula Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Hyderabad
    The result required is - '9CS02'
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    With literal strings this is not elegant:

    SELECT regexp_substr('9CS02 - DARPA C-SCAN II','\d+',1,instr('9CS02 - DARPA C-SCAN II', ' ')-1) FROM dual;

    That will get you the string you want. Obviously if this were selecting a column from a table it would be much simpler and easier to write.
     
  9. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    This other condition.
    Code (SQL):
    SELECT regexp_substr('99886 NEW-PROJ','\S+',1,1) FROM dual;
     
  10. Swapna Sree Nomula

    Swapna Sree Nomula Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Hyderabad
    Thanks krasnoslobodtsev_si!

    Please find the requirement i am looking for -

    I have 3 following variety of strings in the Oracle table - pa_projects_all(Column - Segment1) -

    1. '99886 NEW-PROJ' --- Result required in this string --->99886
    2. '99887-NEW PROJ' --- Result required in this string --->99887
    3. '9CS02 - DARPA C-SCAN II' --- Result required in this string --->9CS02

    SELECT regexp_substr('99886 NEW-PROJ','\S+',1,1) FROM dual; ----Result Achieved
    SELECT regexp_substr('99887-NEW PROJ','\S+',1,1) FROM dual; ----Result Not Achieved
    SELECT regexp_substr('9CS02 - DARPA C-SCAN II','\S+',1,1) FROM dual; ----Result Achieved

    Requirement from Client -
    The parsing should use the logic: Extract the first set of letter or numbers until the parser encounters either a space character, dash (-) or the end of the string.

    As 3 different types of strings are available in a single column, we are finding it difficult.

    Thanks In Advance!

    Swapna Sree Nomula
     
  11. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The second string has no spaces thus the regular expression which works for the other two conditions has nothing to work on. This will require a CASE statement to generate the 'correct' output for all possible string variations. Not having a database available I cannot test expressions; my thought is the following might return the proper result for case 2;

    SELECT regexp_substr('99887-NEW PROJ','\-+',1,1) FROM dual;

    but that will need to be verified.
     
  12. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Code (SQL):

    WITH your_data (str) AS
    (
    SELECT '99886 NEW-PROJ' FROM dual UNION ALL
    SELECT  '99887-NEW PROJ' FROM dual UNION ALL
    SELECT 'S99886  NEW-PROJ' FROM dual UNION ALL
    SELECT  'L99887+ NEW PROJ' FROM dual UNION ALL
    SELECT  '9CS02/DARPA C-SCAN II' FROM dual
    )
    SELECT
      regexp_substr(str,'\w+',1,1)  str
    FROM your_data;

    SQL>
    STR
    ------------
    99886
    99887
    S99886
    L99887
    9CS02

    or

    Code (SQL):
    WITH your_data (str) AS
    (
    SELECT '99886 NEW-PROJ' FROM dual UNION ALL
    SELECT  '99887-NEW PROJ' FROM dual UNION ALL
    SELECT 'S99886  NEW-PROJ' FROM dual UNION ALL
    SELECT  'L99887- NEW PROJ' FROM dual UNION ALL
    SELECT  '9CS02 - DARPA C-SCAN II' FROM dual
    )

    SELECT
        regexp_substr(str,'[^([:space:] |[:punct:])]+',1,1)
    FROM your_data;

    SQL>
    STR--------------------
    99886
    99887
    S99886
    L99887
    9CS02
     
     
    Last edited: Jun 2, 2016
  13. Swapna Sree Nomula

    Swapna Sree Nomula Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Hyderabad
    Thanks a lot krasnoslobodtsev_si!

    I will try the options provioded, thanks a lot for all your time and Knowledge!

    Swapna Sree Nomula