Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2012
    Posts
    14

    Question Unanswered: need to filter data by limit or count in table

    i have to filter data by count, that have to fileter data insze by 13 digist in a coloumn from a table, can anyone help me in this..
    have to get the data which is size in 13 digits...

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    What is "data"? If you mean column, what is its data type? Can you post some sample values?
    What "data" is "13 digits"? May they be separated/padded by another characters?

    Maybe it would be helpful, if you read this article before posting any question: http://tkyte.blogspot.com/2005/06/ho...questions.html
    Pay special attention to the "Context" paragraph. Then, please try to post relevant parts of your problem. Do not hesitate to also post DDL/DML statements - yes, we do understand Oracle SQL here.

    Because, now it is impossible to deduce anything (input, applied rules, output) from your post, so it is also impossible to provide you any meaningful suggestion/solution.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If something as simple as the following example does what you are looking for, fine. If not, do what Flyboy told you.
    Code:
    SQL> with test as
      2    (select 123 col from dual union
      3     select 12345 from dual union
      4     select 123456789 from dual
      5    )
      6  select col
      7  from test
      8  where length(to_char(col)) = 5;
    
           COL
    ----------
         12345
    
    SQL>

  4. #4
    Join Date
    Feb 2012
    Posts
    14

    example

    this is the example

    mu coloumns has a set of digits like

    H14526358964

    data type - string

    i have to filter data by limit, like it should be in 12 digits only.. like wise i have to filter ..

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    So LENGTH it is.

  6. #6
    Join Date
    Feb 2012
    Posts
    14

    yes

    yes is is length, but i canget the length by using length function, but the issue is, some of the datas having emply spaces with them, bt using length those datas are also getting displayed. i have to find the data length and also i have to omit the spaces. and can anyone tell me the exact use of trim().

  7. #7
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by extremeashok View Post
    can anyone tell me the exact use of trim().
    The manual?

    http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions219.htm#i79689

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    How about REPLACE?
    Code:
    SQL> with test as
      2    (select 'H123' col from dual union
      3     select 'A 12 45  ' from dual union
      4     select 'B123456 789' from dual union
      5     select 'C 1  2   3    4  ' from dual
      6    )
      7  select col
      8  from test
      9  where length(replace(col, ' ', '')) = 5;
    
    COL
    -----------------
    A 12 45
    C 1  2   3    4
    
    SQL>
    Anyway: if you want to get the most accurate answer, you should provide as many details as possible. First there were numbers, then there were strings, now there are strings with spaces. What will be next?

Posting Permissions

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