CREATE TABLE TableX
(ID varchar(20));
INSERT INTO TableX
VALUES
('3201-20210103001'),
('3201-20210103001_002'),
('3201-20210103001_001'),
('3201-210104001'),
('3201-210104001-ati'),
('3201-210104001_01'),
('3201-210104001_02');
SELECT ID,
d.vals.value('(/TR/TD)[1]','VARCHAR(20)') +'-'+
d.vals.value('(/TR/TD)[2]','VARCHAR(20)') 'ID_1'
FROM (
SELECT ID,
CAST('<TR><TD>' + REPLACE(REPLACE(ID,'_','-'), '-', '</TD><TD>') + '</TD></TR>' AS xml) vals
FROM TableX) AS d
DECLARE @s varchar(max)
set @s = '12345-abcde_36789'
-- 2014版之後適用
select top 1 * from STRING_SPLIT(@s,'_')
-- 2014版(含)之前適用
SELECT LEFT(@s, charindex('_', @s) - 1)
恩...我也來玩一下@@"
declare @Tmp table(
StrNo nvarchar(50)
)
insert into @Tmp
values('3201-20210103001'),
('3201-20210103001_002'),
('3201-20210103001_001'),
('3201-210104001'),
('3201-210104001-ati'),
('3201-210104001_01'),
('3201-210104001_02');
select *
,left(StrNo,PatIndex('%[-]%',StrNo))
+ left(
replace(StrNo,left(StrNo,PatIndex('%[-]%',StrNo)),'')
,
(
case when PatIndex('%[-_]%',replace(StrNo,left(StrNo,PatIndex('%[-]%',StrNo)),'')) = 0
then len(replace(StrNo,left(StrNo,PatIndex('%[-]%',StrNo)),''))
else PatIndex('%[-_]%',replace(StrNo,left(StrNo,PatIndex('%[-]%',StrNo)),'')) - 1
end
)
) TmpStr
from @Tmp