Select part of a field after the last occurrence o a certain character in MSSQL

Question

I have a field that contains a log of changes. every time adds the date between square brackets.

I need to extract the last bit of the filed after the last ] to put it into a table.

I was trying this but it doesn't work...

select
SUBSTRING(BUG.CloseDescription,0,PATINDEX(']',bug.CloseDescription)) as myString
from Bug

Any ideas?

thanks

Answer 1

declare @field varchar(100)
set @field = 'test [2012-02-10 10:00] wibble'
select right(@field, charindex('[',@field))

or to get the date

declare @field varchar(100)
set @field = 'test [2012-02-10 10:00] wibble'
select substring(@field, charindex('[',@field)+1, charindex(']',@field)-charindex('[',@field)-1)

Answer 2

select
right(BUG.CloseDescription,charindex(']',reverse(BUG.CloseDescription))-1)
from Bug