try~

declare @a table(
	Txt nvarchar(100)
)
insert into @a
values(N'培林5205ZZ8只')
,(N'油封DKB-30477/108片')
,(N'破布A*40KG')
,(N'內外牙彎頭 90。(銅 P-079) 1/4"*6.00個')

select *
,left(
	right(
		txt
		,PATINDEX ('%[0-9]%',REVERSE(txt))
		+ PATINDEX ('%[^0-9.]%',right(REVERSE(txt),len(txt)-(PATINDEX ('%[0-9]%',REVERSE(txt))-1)))
		-2
	)
	,
	PATINDEX ('%[^0-9.]%',right(REVERSE(txt),len(txt)-(PATINDEX ('%[0-9]%',REVERSE(txt))-1))) -1
)
Unit
from @a

https://dbfiddle.uk/ozc-Kz_v

題外話..這是抓不規則吧@@a

首先，是我的話會先去扁開這table的人

另外我看不太懂問題
不知道是我理解能力太差還是怎樣

您說要拆出最後一個＊號後的數字
但以下兩筆資料都沒有＊號
培林5205ZZ8只
油封DKB-30477/108片

最後這段更看不懂:
「油封DKB-30477/10*」但 8片
這是甚麼鬼

需求不夠明確，我只能看您中間的示例來通靈
我猜是想要取得最後一組出現的數字
如果用拆分的方式
我會把資料取出來後用其他程式語言
找出最後一個數字出現的位置，往前遍歷直到出現非數字
最後再輸出就可以了，但我猜效能會炸開

通常應該都是把table schema改成
itemName　培林
itemCode　5205ZZ
amount　　8
unit　　　　只
這樣後續要做其他功能
如查詢Name、Code或是增減數量都方便
不然你code寫到吐就算了，倉儲類的資料通常又不少
效能真的會炸開

這樣?
我的想法跟純真的人一樣，透過正則表示法取資料，只是我透過變數宣告把一行select拆開來。

create function fn_splitFinalNum(@tt nvarchar(125))
returns decimal(10,4)
as begin
	declare @rt nvarchar(32) = reverse(@tt)
	declare @rt2 nvarchar(32) = SUBSTRING(@rt, PATINDEX('%[0-9.]%', @rt), len(@rt))
	return convert(decimal(10, 4), REVERSE(SUBSTRING(@rt2, 0, PATINDEX('%[^0-9.]%', @rt2))))
end
go

declare @a table(
	Txt nvarchar(100)
)
insert into @a
values(N'培林5205ZZ8只')
,(N'油封DKB-30477/108片')
,(N'破布A*40KG')
,(N'內外牙彎頭 90。(銅 P-079) 1/4"*6.00個')

select dbo.fn_splitFinalNum(Txt) from @a