请教sql ，拆分字符串-----多谢！

[lmdotnet]

表t1中包括字段id,字段notes

数据是：

id notes
1 aaaaaaa(1)
2 bbbbbbb(1h)
3 ccccccc(1小时)


我想通过sql查出：

id notes date
1 aaaaaaa 1
2 bbbbbbb 1
3 ccccccc 1

请帮忙，多谢！

[天-笑]

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 行受影响)

[夜予]

先拆分开，然后再对结果进行分组统计

[lmdotnet]

select id,left(notes,charindex('(',notes)-1),
dbo.GET_NUMBER2(notes) as date
from @t

运行失败，notes字段在数据库中是text类型

[水族杰纶]

[Quote=引用 3 楼 的回复:]
select id,left(notes,charindex('(',notes)-1),
dbo.GET_NUMBER2(notes) as date
from @t

运行失败，notes字段在数据库中是text类型
[/Quote]
转varchar
cast(notes as varchar(max))

[quchen520]

[Quote=引用 1 楼 的回复:]

SQL code

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

decl……
[/Quote]+1
如果楼主的数据格式不变。比如 没有这种4 ccccccc(1天)
一楼的代码就能解决了

[zhazhuzhao]

1楼的办法很好！

[lmdotnet]

select dbo.GET_NUMBER2(cast(Notes as varchar(max)))
from t1

运行结果，

1 10
2 10
3 NULL
4 NULL
5 10

[lmdotnet]

原始数据有的是没有(1)的：

例如：

id notes
1 aaaaaa
2 bbbbbb(1)
3 cccccc(1h)
4 dddddd
5 eeeeee(1天)
6 ffffff(1小时)

数据很不规范，希望如果没有（），在查询结果中赋值为0

希望查询结果：

id notes date
1 aaaaaa 0
2 bbbbbb 1
3 cccccc 1
4 dddddd 0
5 eeeeee 1
6 ffffff 1

[天-笑]

select id,left(notes,charindex('(',notes)-1),
        isnull(dbo.GET_NUMBER2(notes),0) as date

from @t

[lmdotnet]

执行结果：
消息 537，级别 16，状态 5，第 1 行
传递给 LEFT 或 SUBSTRING 函数的长度参数无效。

[天-笑]

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
*/

[lmdotnet]

谢谢你
我的数据说的不准确：
notes中的实际数据是：

id notes
1 $#$上午工作内容及耗时(小时)(必填)$#$武汉李四光、深圳敖名恩售前支持。（1小时） 办公用品打包。（0.5小时） $#$下午工作内容及耗时(小时)$#$

最后要的结果是：
id notes date
1 $#$上午工作内容及耗时 0
1 $#$武汉李四光、深圳敖名恩售前支持。 1
1 办公用品打包。 0.5
1 $#$下午工作内容及耗时 0

[中国风]

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
*/