CODEWITHSUNDEEP
sqlsql-server
Haz
Haz

Reputation: 361

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)

Upvotes: 7

Views: 47391

Answers (4)

Saavedra
Saavedra

Reputation: 1


DECLARE @cadena varchar(max)
SET @cadena= 'AB1CAB2CA7274743B3C'
SELECT  @cadena AS Cadena

SELECT   CHARINDEX('B', @cadena) AS PosB1
       , CHARINDEX('B', @cadena, CHARINDEX('B', @cadena) + 1) AS PosB2
       , CHARINDEX('B', @cadena, 
         CHARINDEX('B', @cadena,
         CHARINDEX('B', @cadena) + 1)+1) AS PosB3
       , SUBSTRING(@cadena,
         CHARINDEX('B', @cadena),2) AS Str01
       , SUBSTRING(@cadena,
         CHARINDEX('B', @cadena, 
         CHARINDEX('B', @cadena) + 1),2) AS Str02
       , SUBSTRING(@cadena,
         CHARINDEX('B', @cadena, 
         CHARINDEX('B', @cadena, CHARINDEX('B', @cadena) + 1)+1),2) AS Str03

Upvotes: -1

Anisa Mitre
Anisa Mitre

Reputation: 11

Setting a start location is an excellent idea. However I would use the searchable character first occurrence +1 as a start location as situation may vary. So the solution would be:

Select charindex(' ','Posted/edited: 56 days ago',(charindex(' ', 'Posted/edited: 56 days ago') +1))

The output is ofc 18

Upvotes: 0

StepUp
StepUp

Reputation: 38199

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

Upvotes: 7

Gro
Gro

Reputation: 1683

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

Upvotes: 5

Related Questions