CREATE FUNCTION DBO.GET_NUMBER2(@S VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
WHILE PATINDEX('%[^0-9]%',@S) > 0
BEGIN
set @s=stuff(@s,patindex('%[^0-9]%',@s),1,'')
END
RETURN @S
END
declare @t table (id int ,notes varchar(16))
insert into @t
select 1 ,'aaaaaaa(1)' union all
select 2 ,'bbbbbbb(1h)' union all
select 3 ,'ccccccc(1小时)'
select id,left(notes,charindex('(',notes)-1),
dbo.GET_NUMBER2(notes) as date
from @t
(3 行受影响)
id date
----------- ---------------- ----------------------------------------------------------------------------------------------------
1 aaaaaaa 1
2 bbbbbbb 1
3 ccccccc 1
(3 行受影响)
select id,left(notes,charindex('(',notes)-1),
isnull(dbo.GET_NUMBER2(notes),0) as date
from @t
select id,
case when charindex('(',notes)>= 1 then
left(notes,charindex('(',notes)-1)
else notes
end as notes,
isnull(dbo.GET_NUMBER2(notes),0) as date
from @t
declare @t table (id int ,notes TEXT)
insert into @t
select 1 ,'aaaaaaa(1)' union all
select 2 ,'bbbbbbb(1h)' union all
select 3 ,'ccccccc(1小时)'
SELECT
ID,notes,[Day]=LEFT([Day],PATINDEX('%[^0-9]%',[Day])-1)
FROM
(SELECT
ID,
SUBSTRING(notes,1,CHARINDEX('(',notes)-1) AS notes,
STUFF(CAST(notes AS VARCHAR(max)),1,CHARINDEX('(',notes),'') AS [Day]
FROM @t
)t
/*
ID notes Day
1 aaaaaaa 1
2 bbbbbbb 1
3 ccccccc 1
*/
use Tempdb
go
--> -->
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([id] int,[notes] nvarchar(100))
Insert #T
select 1,N'$#$上午工作内容及耗时(小时)(必填)$#$武汉李四光、深圳敖名恩售前支持。（1小时） 办公用品打包。（0.5小时） $#$下午工作内容及耗时(小时)$#$'
Go
;WITH a
AS
(
Select
[id],substring(a.[notes],b.number,charindex('小时）',a.[notes]+'小时）',b.number)-b.number) AS [notes]
from (select ID,notes FROM #T) AS a,master.dbo.spt_values AS b
WHERE type='P' AND CHARINDEX('小时）','小时）'+a.[notes],b.number)=b.number
)
,b AS
(
SELECT ID,CASE WHEN CHARINDEX('$#$',[notes])>0 THEN STUFF([notes],1,CHARINDEX('$#$',[notes])-1,'') ELSE [notes] END AS [notes]
FROM (Select
[id],substring(a.[notes],b.number,charindex('小时）',a.[notes]+'小时）',b.number)-b.number) AS [notes]
from #T AS a,master.dbo.spt_values AS b
WHERE type='P' AND CHARINDEX('小时）','小时）'+a.[notes],b.number)=b.number) AS a
),c
AS
(
SELECT
ID,LEFT([notes],LEN([notes])-CHARINDEX('(',REVERSE([notes]))) AS [notes],CASE WHEN CHARINDEX('(',REVERSE([notes]))=1 THEN '0' WHEN CHARINDEX('(',REVERSE([notes]))>1 THEN RIGHT([notes],CHARINDEX('(',REVERSE([notes]))-1)END AS [date]
FROM b)
SELECT
ID,[notes],[date]
FROM c
/*
ID notes date
1 $#$上午工作内容及耗时 0
1 $#$武汉李四光、深圳敖名恩售前支持。 1
1 办公用品打包。 0.5
1 $#$下午工作内容及耗时 0
1 $#$ NULL
*/
IF OBJECT_ID('Tempdb..#')IS NOT NULL
DROP TABLE #
;WITH a
AS
(
Select
[id],substring(a.[notes],b.number,charindex('小时）',a.[notes]+'小时）',b.number)-b.number) AS [notes]
from (select ID,notes FROM #T) AS a,master.dbo.spt_values AS b
WHERE type='P' AND CHARINDEX('小时）','小时）'+a.[notes],b.number)=b.number
)
,b AS
(
SELECT ID,CASE WHEN CHARINDEX('$#$',[notes])>0 THEN STUFF([notes],1,CHARINDEX('$#$',[notes])-1,'') ELSE [notes] END AS [notes]
FROM (Select
[id],substring(a.[notes],b.number,charindex('小时）',a.[notes]+'小时）',b.number)-b.number) AS [notes]
from #T AS a,master.dbo.spt_values AS b
WHERE type='P' AND CHARINDEX('小时）','小时）'+a.[notes],b.number)=b.number) AS a
),c
AS
(
SELECT
ID,LEFT([notes],LEN([notes])-CHARINDEX('(',REVERSE([notes]))) AS [notes],CASE WHEN CHARINDEX('(',REVERSE([notes]))=1 THEN '0' WHEN CHARINDEX('(',REVERSE([notes]))>1 THEN RIGHT([notes],CHARINDEX('(',REVERSE([notes]))-1)END AS [date]
FROM b)
SELECT
ID,[notes],[date]
INTO #
FROM c
SELECT * FROM # WHERE date IS NOT null
/*
ID notes date
1 $#$上午工作内容及耗时 0
1 $#$武汉李四光、深圳敖名恩售前支持。 1
1 办公用品打包。 0.5
1 $#$下午工作内容及耗时 0
*/