请教sql ,拆分字符串-----多谢!

lmdotnet 2012-07-13 02:02:45
表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

请帮忙,多谢!
...全文
247 15 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
15 条回复
切换为时间正序
请发表友善的回复…
发表回复
中国风 2012-07-16
  • 打赏
  • 举报
回复
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
*/
中国风 2012-07-16
  • 打赏
  • 举报
回复
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
*/
lmdotnet 2012-07-13
  • 打赏
  • 举报
回复
谢谢你
我的数据说的不准确:
notes中的实际数据是:

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

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



中国风 2012-07-13
  • 打赏
  • 举报
回复
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
*/
天-笑 2012-07-13
  • 打赏
  • 举报
回复



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



lmdotnet 2012-07-13
  • 打赏
  • 举报
回复
执行结果:
消息 537,级别 16,状态 5,第 1 行
传递给 LEFT 或 SUBSTRING 函数的长度参数无效。
天-笑 2012-07-13
  • 打赏
  • 举报
回复


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

from @t



lmdotnet 2012-07-13
  • 打赏
  • 举报
回复
原始数据有的是没有(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
lmdotnet 2012-07-13
  • 打赏
  • 举报
回复
select dbo.GET_NUMBER2(cast(Notes as varchar(max)))
from t1

运行结果,

1 10
2 10
3 NULL
4 NULL
5 10
zhazhuzhao 2012-07-13
  • 打赏
  • 举报
回复
1楼的办法很好!
quchen520 2012-07-13
  • 打赏
  • 举报
回复
[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天)
一楼的代码就能解决了
水族杰纶 2012-07-13
  • 打赏
  • 举报
回复
[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))
lmdotnet 2012-07-13
  • 打赏
  • 举报
回复
select id,left(notes,charindex('(',notes)-1),
dbo.GET_NUMBER2(notes) as date
from @t

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

夜予 2012-07-13
  • 打赏
  • 举报
回复
先拆分开,然后再对结果进行分组统计
天-笑 2012-07-13
  • 打赏
  • 举报
回复

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

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧