Getting the second occurrence from charindex function in sql server

Question

I need to get the second occurrence of the space for below text. It should be the space after the 56, but I'm getting 15th position before the 56 as the first one.

select charindex(' ', 'Posted/edited: 56 days ago', 2)

Answer 1

You need to set START_LOCATION for CHARINDEX. It means after what character charindex should be found. In our example, we need to find after 56. So the code should looks like this:

select CHARINDEX(' ', 'Posted/edited: 56 days ago', 
    CHARINDEX('56', 'Posted/edited: 56 days ago', 0));

OUTPUT:

18

Answer 2

You are already getting position of the second space (' ') in your query => 15. To clarify for example, you can use it to extract content from that point onwards, using following

select substring('Posted/edited: 56 days ago', 
           charindex(' ', 'Posted/edited: 56 days ago', 2) + 1, 
           len('Posted/edited: 56 days ago'))