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!

How to remove preceeding #'s from a word

Discussion in 'SQL PL/SQL' started by Shanmugapriya, May 1, 2017.

  1. Shanmugapriya

    Shanmugapriya Active Member

    Messages:
    55
    Likes Received:
    0
    Trophy Points:
    130
    Location:
    Bangalore
    Hi,
    I would like to know how to remove preceeding #'s from a word

    ex 1: '####oracle'
    ex 2: '##oracle'
    ex 3:'#oracle'

    Output should be :'oracle' in all the above cases. Preceeding #'s can be in any number of places.



    Please advise
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    768
    Likes Received:
    147
    Trophy Points:
    830
    Location:
    Russian Federation
    it's very simple.use the function LTRIM
    Code (Text):


    WITH your_data(ex) AS
    (
      SELECT  '####oracle' FROM dual UNION ALL
      SELECT  '##oracle'FROM dual UNION ALL
      SELECT '#oracle' FROM dual
    )
    SELECT  rownum,LTRIM(ex,'#') FROM your_data ;

     
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,639
    Likes Received:
    368
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    SUBSTR() and INSTR() can also be used:

    Code (SQL):
    WITH your_data(ex) AS
    (
      SELECT  '####oracle' FROM dual UNION ALL
      SELECT  '##oracle'FROM dual UNION ALL
      SELECT '#oracle' FROM dual
    )
    SELECT  substr(ex, instr(ex, '#', -1,1)+1) FROM your_data ;

     
     
  4. ramsharma84

    ramsharma84 Newly Initiated

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    india
    WITH your_data AS
    (
    select '####oracle###' a from dual union all
    select '##oracle##' a from dual union all
    SELECT '#oracle##' a FROM dual
    )
    SELECT trim('#' from a ) FROM your_data ;