locked
Finding last occurrence of a space in a string RRS feed

  • Question

  • Hello,

    I need to write a script where I can find the last occurrence of a space in a particular column. I figured out how to find the first occurrence, but I am having difficulty with finding the last occurrence. Any help would be appreciated...

    Thanks,

    Dave
    Dave SQL Developer
    Tuesday, February 9, 2010 9:13 PM

Answers

All replies

  • Use the REVERSE string function and search like for the first occurrence.

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012






    • Proposed as answer by Naomi N Tuesday, February 9, 2010 9:34 PM
    • Edited by Kalman Toth Monday, September 24, 2012 10:20 PM
    Tuesday, February 9, 2010 9:19 PM
  • Thanks for your quick response.

    I need to select everything that is before the last instance of the space.

    Example.. If the string is "Joe writes C# code", I would need "Joe writes C#". I thought of the reverse function, but I don't think it will help me here.

    Thanks again.

    Dave
    Dave SQL Developer
    Tuesday, February 9, 2010 9:41 PM
  • Actually, I think Kalman's answer is just fine.  For example:

    declare @var varchar(20)  set @var = 'Joe writes C# code'
    
    select 
      @var [@var],
      reverse(rtrim(substring(reverse(@var), 1, charindex(' ', reverse(@var)))))
      [Last Segment]
    
    /* -------- Output: --------
    @var                 Last Segment
    -------------------- --------------------
    Joe writes C# code   code
    */
    

    EDIT:

    Works to extract the last word, but this is not what you are after.  sorry.
    Tuesday, February 9, 2010 9:46 PM
  • Try:

    DECLARE @s varchar(128);
    
    SET @s = 'Joe writes C# code';
    
    SELECT
    	@s,
    	LEFT(@s, LEN(@s) - CHARINDEX(' ', REVERSE(@s)));
    GO

     

    AMB

    • Proposed as answer by Naomi N Tuesday, February 9, 2010 10:20 PM
    Tuesday, February 9, 2010 9:49 PM
  • Check out the following solution:

    DECLARE @string nvarchar(max) = 'Joe writes C# code'
    
    SELECT REVERSE(RIGHT(REVERSE (@string), len(@string) - 
          NULLIF(charindex(' ', REVERSE(@string)),0)))
    -- Joe writes C#

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012






    • Marked as answer by DaveDB Tuesday, February 9, 2010 10:10 PM
    • Edited by Kalman Toth Monday, September 24, 2012 10:19 PM
    Tuesday, February 9, 2010 9:50 PM
  • Thank you!

    Dave SQL Developer
    Tuesday, February 9, 2010 10:11 PM
  • note this can easliy be changed to find the last of any string a space being an example

    SELECT REVERSE(RIGHT(REVERSE (@string), len(@string) -
          NULLIF(charindex(@findstring, REVERSE(@string)),0)))

    or even

    SELECT REVERSE(RIGHT(REVERSE (@string), len(@string) -
          NULLIF(charindex(REVERSE(@findstring), REVERSE(@string)),0)))

    nice work Kalman
    Wednesday, February 10, 2010 3:20 PM