Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: using SUBSTR to extract a string.....10g

    Hi

    Could someone please advise if it is possible to extract a string from the values in a colulmn of a table using SUBSTR in a query?

    Can I write a select query in Toad to do the below-mentioned?

    I've written the SUBSTR statement but don't know how to specify the 'Search String' i.e., COL1 so that the values in COL1 are used.

    E..g,

    I want to use SUBSTR to extract the middle character from all the rows in COL1 in TABLE A.

    Code:
    TABLE A
    DATETIME, COL1  
    DATE         ABC
    DATE         DEF
    DATE         GHI
    DATE         JKL
    Regards
    Shajju

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If COL1 value is ABC, the middle character is B.

    What is the middle character of ABCD?

  3. #3
    Join Date
    Aug 2008
    Posts
    464
    Sorry if the example table was a terrible one.

    The column containing the search strings only contains a certain pattern in which there are 9 slashes '/'.

    I was asking how to specify a column as the search string in a query that uses substr/instr?

    Code:
    select  SUBSTR(COLUMN, INSTR (COLUMN,'/',-2,1)+1,INSTR(COLUMN,'/',1,9)-INSTR(COLUMN,'/',1,8)-1)
    from TABLE where schema='SCHEMA1'
    Regards
    Shajju
    Last edited by shajju; 10-22-13 at 06:20.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Sorry, but I don't understand what you are saying. However, a more meaningful test case would certainly help. Specify several inputs and desired outputs.

  5. #5
    Join Date
    Aug 2008
    Posts
    464
    OK. Maybe I wasn't clear about the question. Say there is a table that contains 1000 records (each containing 3 characters):

    Code:
    TABLE A
    DATETIME, COL1  
    DATE         ABC
    DATE         DEF
    DATE         GHI
    DATE         JKL
    and I want to select the middle character from all the values in COL1 using the SUBSTR function.


    QUESTION

    Is there a way to specify the column instead of using each of the 1000 values in the select statement?

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I agree with Littlefoot.
    Quote Originally Posted by Littlefoot View Post
    Sorry, but I don't understand what you are saying. However, a more meaningful test case would certainly help. Specify several inputs and desired outputs.
    Please post both of some input data and desired outputs from the data.

    At least, what result do you want from your 4 rows data?
    Code:
    TABLE A
    DATETIME, COL1  
    DATE         ABC
    DATE         DEF
    DATE         GHI
    DATE         JKL

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Huh, middle character from a 3-character string is a simple task, but I'm not sure that this is what you are looking for.
    Code:
    SQL> with test as
      2    (select 'abc' col from dual union
      3     select 'def'     from dual
      4    )
      5  select col,
      6         substr(col, 2, 1) middle
      7  from test;
    
    COL M
    --- -
    abc b
    def e
    
    SQL>
    When do these "certain patterns" and "9 slashes" come into the plot?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •