how to return the last word in a string
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: how to return the last word in a string

  1. #1
    Join Date
    Nov 2000
    Location
    Atlanta, GA, USA
    Posts
    85

    how to return the last word in a string

    Hello,
    I needed to return the first and last word in any given string. Words are separated by a single space. I was able to return the first word but need help for returning the last word.

    Code:
    The desired output is as follows:
    Str1                    First        Last
    -----------------       ---------    --------- 
    Blue sky                Blue         sky
    red 1. green            red          green
    The color is blue       The          blue 
    My name is John         My           John
    
    
    select 
    substr(Str1,1,instr(Str1, ' ')-1) First,
    substr(Str1,-1,instr(substr(Str1,-1),' ')+1) Last
    from Table1;
    
    The above sql statement output is as follows:
    Str1                    First        Last
    -----------------       ---------    --------- 
    Blue sky                Blue         y
    red 1. green            red          n
    The color is blue       The          e 
    My name is John         My           n
    it returns the last character of the word and not the whole word.
    Thanks for your help !

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Untested:
    substr(Str1, instr(Str1, ' ', -1)+1) Last
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  3. #3
    Join Date
    Nov 2000
    Location
    Atlanta, GA, USA
    Posts
    85
    That did it !
    Thanks for your help DaPi !

Posting Permissions

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



Click Here to Expand Forum to Full Width