你一定要用SQL處理嗎？
不能用後端處理？

利用數值+字串會成為NULL的道理去選擇 是不是這樣??
左取7包右取5 其中左取3(加上右取2)不是NULL 就是有值，就取右5

而左取3加上右取2包左取1不是NULL 就是有值，就取右4 其他的就取3

CASE LEFT(RIGHT(LEFT(A,7),5),3)+RIGHT(RIGHT(LEFT(A,7),5),2) WHEN NULL THEN (CASE LEFT(RIGHT(LEFT(A,7),5),3)+LEFT(RIGHT(RIGHT(LEFT(A,7),5),2),1) WHEN NULL THEN LEFT(RIGHT(LEFT(A,7),5),3) ELSE LEFT(RIGHT(LEFT(A,7),5),4) END) ELSE RIGHT(LEFT(A,7),5) END

CREATE TABLE tbltest
    (`PONO` varchar(50))
;
    
INSERT INTO tbltest
    (`PONO`)
VALUES
    ('AA123'),
    ('AA1234'),
    ('AA12345'),
    ('AA123()'),
    ('AA1234(AA789)'),
    ('AA12345(AA789)'),
    ('AA1234(ABCD4321)'),
    ('AA123(CCBASD999999)'),
    ('AA12345'),
    ('AA1234567<321CHANGE987>')
;

SELECT 
CASE instr(pono,'(') 
WHEN 0 THEN SUBSTR(pono, 3)
ELSE SUBSTR(pono, 3, INSTR(PONO,'(')-3)
END
FROM tbltest

什麼？
不確定有沒有且沒固定格式與字數的字元？
那大概得找Siri來解決了...

https://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql
https://github.com/mysqludf/lib_mysqludf_preg

其他資料庫有regexp replace，不過mysql好像沒有...
上面的不知道還有沒有用，我不用mysql。

您好,我是SQL新手,以下SQL程式碼應該有符合您的需求
但因為是新手所以可能寫得太複雜..有需要改進的地方再麻煩各位提點一下喔!
(環境:MS SQL SEVER2000)

SET NOCOUNT ON
-- 宣告變數
DECLARE @test1 varchar(30) ,@idx int

SET @idx = 3

-- 建表給值
DECLARE @RESULT_TABLE TABLE (tst_char VarChar(50));
DECLARE @pratice_tmp TABLE (tst_char VarChar(50));
INSERT INTO @pratice_tmp VALUES ('AA54321~~spf25254joisjo)')
INSERT INTO @pratice_tmp VALUES ('AB1234ever1th1ng')
INSERT INTO @pratice_tmp VALUES ('AC21測試test321>!<')
INSERT INTO @pratice_tmp VALUES ('BA787321。句號試試看111')
INSERT INTO @pratice_tmp VALUES ('CA54AAW11#@$5254jsjo)')
INSERT INTO @pratice_tmp VALUES ('DD08081456`11~5sjo`.')

-- 定義指標
DECLARE CSR_CATCH_NAME CURSOR FOR SELECT tst_char FROM @pratice_tmp

-- 開啟指標
OPEN CSR_CATCH_NAME 

-- 開始擷取資料
FETCH NEXT FROM CSR_CATCH_NAME INTO @test1;

WHILE @@FETCH_STATUS = 0  
BEGIN --取到非中英的字元為止
	WHILE (SUBSTRING(@test1,@idx,1) like '[A-Z]') OR 
		  (SUBSTRING(@test1,@idx,1) like '[a-z]') OR 
		  (SUBSTRING(@test1,@idx,1) like '[0-9]')	
	BEGIN
		SET @idx = @idx + 1;
	END

	INSERT INTO @RESULT_TABLE VALUES (SUBSTRING(@test1,3,@idx-3))
	SET @idx = 3

	FETCH NEXT FROM CSR_CATCH_NAME INTO @test1;
END

SELECT tst_char AS '編號' FROM @RESULT_TABLE

SET NOCOUNT OFF

https://blog.51cto.com/qubernet/5160826
參考此方式先設sql function 再去用function取欄位其數字部分
剛好也碰上發問者的問題找到解答。但處理的速度會較慢...