Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2010
    Posts
    49

    Unanswered: Return characters after fullstop

    Hi,

    I have a column that contains characters separated with a fullstop for example

    ABCD.1df
    12hgglk.a23
    bcd.sdfgh

    What I want to do is only return the characters after the full stop, the results could be between 0 and 15 charcters long (some fields will be NULL)

    I think I need to use InStr to find the lcoation of the fullstop, but am then unsure how to get the charcters after it.

    Can anyone point me in the right direction?

  2. #2
    Join Date
    Oct 2010
    Posts
    49
    Okay,

    This works, except when there is no fullstop it returns the full text, I'd rather it returned nil where fullstop was not found.


    SELECT sekey,SubStr(sekey,InStr(SEKEY,'.')+1) FROM HERE

  3. #3
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    several ways you could do this, one way you could use decode

    decode(instr(sekey,'.'), 0, null, (substr(sekey,instr(sekey,'.')+1)))

    so if there is no . in the string it just retuns null
    There are 10 types of people in the world, those that know Binary and those that don't.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Regular expressions are your friends:
    Code:
    SQL> select
      2    col,
      3    regexp_substr(col, '[^.]+', 1, 2) col_new
      4  from test;
    
    COL                            COL_NEW
    ------------------------------ ------------------------------
    ABCD.1df                       1df
    12hgglk.a23                    a23
    bcd.sdfgh                      sdfgh
    xzy

  5. #5
    Join Date
    Oct 2010
    Posts
    49
    big thanks to robert xr4x4 & littlefoot.


    Both solutions work perfectly.

    Cheers

  6. #6
    Join Date
    Oct 2010
    Posts
    49
    Robert,

    Using your solution how can I get this to work in reverse.(i.e give me everything before the fullstop)

    I've tried using Instrrev or Instrev (not sure which is correct) but neither are recognised as valid commands?

  7. #7
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    substr works like this ... substr(string, start, num_characters)

    so

    substr(sekey, 1, instr(sekey,'.')+1)
    There are 10 types of people in the world, those that know Binary and those that don't.

  8. #8
    Join Date
    Oct 2010
    Posts
    49
    Excellent,

    I changed the last value to -1 and it works. Knew the solution was simple still I wont forget I've spent 4 hours trying to figure it out!

    substr(sekey,1,inStr(sekey,''.'')-1)))

    Thanks

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Regular expressions:
    Code:
    SQL> select
      2    col,
      3    regexp_substr(col, '[[:alnum:]]+') col_new
      4  from test;
    
    COL                  COL_NEW
    -------------------- -------------------------------
    ABCD.1df             ABCD
    12hgglk.a23          12hgglk
    bcd.sdfgh            bcd
    xyz                  xyz

Posting Permissions

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