CODEWITHSUNDEEP
sqlsql-serversubstring
user36729
user36729

Reputation: 575

SQL substring after second occurrence

I have strings such as below:

Geographical Information & Income: Income - National Classifications: Los Angeles - Low

Do you know how can only get "Low" from this? This is difficult for me since I don't know how I can tell substring to start from second "-".

Upvotes: 0

Views: 1806

Answers (4)

Himanshu
Himanshu

Reputation: 3970

This is valid for 2nd index of "-" 

     Select
            Substr(
          Instr(
                Substr(
                Instr(Substr(string, "-")+1, 
                Length(string)
                            ), 
            "-")+1 , 
            length(string)) from table;

Upvotes: 0

VBoka
VBoka

Reputation: 9083

I suggest to use trim in combination:

select trim(RIGHT ( 'Geographical Information & Income: Income - National Classifications: Los Angeles - Low ' , charindex('-', reverse('Geographical Information & Income: Income - National Classifications: Los Angeles - Low '))-1) )

Why ? Well because you can have a case like this 'test - test - word ' Cheers!

Upvotes: 0

Icculus018
Icculus018

Reputation: 1066

You can get the far right word by using RIGHT:

DECLARE @string NVARCHAR(500) = 'Geographical Information & Income: Income - National Classifications: Los Angeles - Low'

SELECT RIGHT(@string,CHARINDEX(' ',REVERSE(@string)) - 1)

Upvotes: 0

forpas
forpas

Reputation: 164089

For this string, use string functions like this:

declare @s varchar(100) = 'Geographical Information & Income: Income - National Classifications: Los Angeles - Low';

select ltrim(right(@s, charindex('-', reverse(@s)) - 1))

Upvotes: 1

Related Questions