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!

trim or remove records and spaces

Discussion in 'General' started by imransi17@gmail.com, Nov 27, 2013.

  1. Hello,

    I am trying to remove/trim everything after 7 characters. using Oracle 11 and 11g, Oracle SQL Developer.

    **issue #1** removing everything after 6 or 7 chracters
    example: 1Q7 4B7 MY NAME IS MARY

    Results I want: 1Q7 4B7

    **issue #2** removing one space
    example: EQ9 2IQ
    Results I want: EQ9 2IQ

    Please assist.

    Thanks
     
  2. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    There's no definitive difference between Issue 1 and 2.

    You could use SQL*Loader I believe, write a package/procedure or SQL writing SQL to get the job done. Your answer will depend on a lot of variables such as the total number of records you must act on and whether or not this will be done on a recurring basis.

    HTH

    CJ
     
  3. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    Come to think of it...you don't necessarily have to do this at all...just use SUBSTR when displaying the record(s) when you grab them. Really depends on the real requirement.

    CJ
     
  4. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    For your first issue:

    select substr('1Q7 4B7 MY NAME IS MARY',1,7) from dual;

    I don't understand what is your second issue.
     
  5. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Imran,

    Where is the space in second issue ?? Can you please point out the place where we can see the space by that we can provide the possible solution here..