Home/ Ahmed_Shmes/Answers
  1. Asked: April 13, 2017In:

    Using between on sysdate instead of TRUNC

    Ahmed_Shmes
    Ahmed_Shmes Junior ahmedshmes21@gmail.com

    Use TO_DATE Function. SELECT sum(column3) from table1 where column1 = ‘XXXXXAA12’ and column2 between to_date (<yor val>,<your format>) and to_date (<yor val>,<your format>);

    Use TO_DATE Function.

    SELECT sum(column3)
    from table1
    where column1 = ‘XXXXXAA12’
    and column2 between to_date (<yor val>,<your format>) and to_date (<yor val>,<your format>);

    See less
  2. Asked: July 14, 2017In:

    How to order by number inside of character for a query like ’25 AB’ ,’30 MT’ ?

    Ahmed_Shmes
    Ahmed_Shmes Junior ahmedshmes21@gmail.com
    This answer was edited.

    Use REGEXP_SUBSTR  Syntax : REGEXP_SUBSTR( string, pattern [, start_position [, nth_appearance [, match_parameter [, sub_expression ] ] ] ] ) code : SELECT t.*, REGEXP_SUBSTR (COL1, '(d+)') as sort_by_col FROM test_table t Order by sort_by_col ; Result : application2 25 AB 30 AB 3125 50 50000 AS740TRead more

    Use REGEXP_SUBSTR 

    Syntax :
REGEXP_SUBSTR( string, pattern [, start_position [, nth_appearance [, match_parameter [, sub_expression ] ] ] ] )
code :
SELECT t.*, REGEXP_SUBSTR (COL1, '(d+)') as sort_by_col
  FROM test_table t
Order by sort_by_col ;
Result :
application2
25 AB
30 AB
3125
50
50000
AS740TRN
QAS897ZCS
oraask
test
    See less
  3. Asked: March 4, 2017In:

    How to convert text with comma separated to array in pl/sql ?

    Ahmed_Shmes
    Ahmed_Shmes Junior ahmedshmes21@gmail.com

    Use REGEXP_SUBSTR  to Extract desired info REGEXP_SUBSTR( string, pattern [, start_position [, nth_appearance [, match_parameter [, sub_expression ] ] ] ] ) And CONNECT BY  to loop over the string searching for the "," and use it as a delimiter. SELECT REGEXP_SUBSTR ('text1, text2, text3', '[^,]+',Read more

    Use REGEXP_SUBSTR  to Extract desired info

    REGEXP_SUBSTR( string, pattern [, start_position [, nth_appearance [, match_parameter [, sub_expression ] ] ] ] )

    And CONNECT BY  to loop over the string searching for the “,” and use it as a delimiter.

    SELECT REGEXP_SUBSTR (‘text1, text2, text3’,
    ‘[^,]+’,
    1,
    LEVEL),LEVEL
    FROM DUAL
    CONNECT BY REGEXP_SUBSTR (‘text1, text2, text3’,
    ‘[^,]+’,
    1,
    LEVEL)
    IS NOT NULL;

    See less