Lokesh Lokesh - 2 months ago 8
SQL Question

how to get a substring between certain characters in sql server

I have a string "com.test.edu.personal.SomeException: this is some error: test message". I need to get the string as "SomeException: this is some error". I am having trouble.

declare @col varchar(100)
set @col = 'com.test.edu.personal.SomeException: this is some error: test message'
SELECT SUBSTRING(@col,
LEN(@col) - CHARINDEX(':', @col) - CHARINDEX(':', REVERSE(@col)),
LEN(@col) - LEN(LEFT(@col, CHARINDEX ('.', @col))) - LEN(RIGHT(@col, LEN(@col) - CHARINDEX (':', @col))));


I am getting "nal.SomeException: this is some " - I am missing something here

Answer
declare @col varchar(100)
set @col = 'com.test.edu.personal.SomeException: this is some error: test message'

Select Substring(@col,Len((substring(@col,1,charindex(':',@col)))) - CharIndex(Reverse('.'), Reverse((substring(@col,1,charindex(':',@col)))))-len('.')+3 ,len(@col))

Returns

SomeException: this is some error: test message