sqlsql-server

Getting the second occurrence from charindex function in sql server

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)
Solution

  • 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