Extract a variable length part of a string containing special characters in TSQL

1

I have the following data in a column:
20191250563622**TB
20192DSGN**DIPTS.

I need the 3rd set of data after the * to form a column.

My output would be:
250
DSGN

I have been trying several forms of substr( Index()) and Substr(instr()) but cannot display this correctly. Please help.

Thank you

2

so what's the rule here? lets say you have 10K rows, that rule should apply to all 10K rows

3

The second one looks like the data up to the * but omitting the first 5 characters
The first omits the first 5 chars then takes the next 3 - not sure why.
Your description about the data after the * doesn't look like either

The second is something like

select right(col,len(col)-5)
from
(
select col = left(col,charindex('',col)-1) from tbl where charindex('',col) > 1
) a

4

this is so random and oogly!

create table #rocky(blob varchar(150));

insert into #rocky
select '20191250563622**TB' union
select '20192DSGN**DIPTS.'

;with oogly
as
(
	select substring(left(blob,charindex('*',blob,0)-1),6, len(blob)) as slice_dice, blob
	  from #rocky
)

select case 
	    when ISNUMERIC(slice_dice) = 0 then slice_dice
		else left(slice_dice, 3)
		end
  from oogly

drop table #rocky
5

Thank you