表中有一列内容如下:
{72919,"普通论坛区",0}{72945,"时尚与美食",0}
{72919,"普通论坛区",0}{72930,"驴游天下",0}{73128,"驴友驿站",0}
{72919,"普通论坛区",0}{72926,"金碧杂谈",0}
{72920,"金碧俱乐部",0}{72930,"驴游天下",0}{73129,"驴行招呼站",0}
{72919,"普通论坛区",0}{72964,"奥运专区",0}{72965,"云南网奥运博客",0}
{72920,"金碧俱乐部",0}{72930,"驴游天下",0}
{72919,"普通论坛区",0}{72953,"论坛DIY",0}{72954,"昆明MOTOK摩托车车友会",0}{72955,"整车 配件 用品交易",0}
... ...
想要的结果是最后一个大括号里面的数字序列,比如以上的结果就应该是:
72945
73128
72926
73129
72965
72930
72955请各位高手不吝赐教。
{72919,"普通论坛区",0}{72945,"时尚与美食",0}
{72919,"普通论坛区",0}{72930,"驴游天下",0}{73128,"驴友驿站",0}
{72919,"普通论坛区",0}{72926,"金碧杂谈",0}
{72920,"金碧俱乐部",0}{72930,"驴游天下",0}{73129,"驴行招呼站",0}
{72919,"普通论坛区",0}{72964,"奥运专区",0}{72965,"云南网奥运博客",0}
{72920,"金碧俱乐部",0}{72930,"驴游天下",0}
{72919,"普通论坛区",0}{72953,"论坛DIY",0}{72954,"昆明MOTOK摩托车车友会",0}{72955,"整车 配件 用品交易",0}
... ...
想要的结果是最后一个大括号里面的数字序列,比如以上的结果就应该是:
72945
73128
72926
73129
72965
72930
72955请各位高手不吝赐教。
if object_id('tempdb.dbo.#') is not null drop table #
create table #(c varchar(200))
insert into #
select '{72919,"普通论坛区",0}{72945,"时尚与美食",0}' union all
select '{72919,"普通论坛区",0}{72930,"驴游天下",0}{73128,"驴友驿站",0}' union all
select '{72919,"普通论坛区",0}{72926,"金碧杂谈",0}' union all
select '{72920,"金碧俱乐部",0}{72930,"驴游天下",0}{73129,"驴行招呼站",0}' union all
select '{72919,"普通论坛区",0}{72964,"奥运专区",0}{72965,"云南网奥运博客",0}' union all
select '{72920,"金碧俱乐部",0}{72930,"驴游天下",0}' union all
select '{72919,"普通论坛区",0}{72953,"论坛DIY",0}{72954,"昆明MOTOK摩托车车友会",0}{72955,"整车 配件 用品 交易",0}'select c = left(c, patindex('%[^0-9]%',c)-1) from
(select c=substring(c,len(c)-charindex('{',reverse(c))+2,len(c)) from #) t/*
c
----------
72945
73128
72926
73129
72965
72930
72955
*/
if object_id('tempdb.dbo.#') is not null drop table #
create table #(c varchar(200))
insert into #
select '{72919,"普通论坛区",0}{72945,"时尚与美食",0}' union all
select '{72919,"普通论坛区",0}{72930,"驴游天下",0}{73128,"驴友驿站",0}' union all
select '{72919,"普通论坛区",0}{72926,"金碧杂谈",0}' union all
select '{72920,"金碧俱乐部",0}{72930,"驴游天下",0}{73129,"驴行招呼站",0}' union all
select '{72919,"普通论坛区",0}{72964,"奥运专区",0}{72965,"云南网奥运博客",0}' union all
select '{72920,"金碧俱乐部",0}{72930,"驴游天下",0}' union all
select '{72919,"普通论坛区",0}{72953,"论坛DIY",0}{72954,"昆明MOTOK摩托车车友会",0}{72955,"整车 配件 用品 交易",0}'--一、
select c = left(c, patindex(',',c)-1) from
(select c=substring(c,len(c)-charindex('{',reverse(c))+2,len(c)) from #) t--二、
select c = left(c, charindex(',',c)-1) from
(select c=substring(c,len(c)-charindex('{',reverse(c))+2,len(c)) from #) t--三、
select c = substring(c,1, charindex(',',c)-1) from
(select c=substring(c,len(c)-charindex('{',reverse(c))+2,len(c)) from #) t
/*
c
----------
72945
73128
72926
73129
72965
72930
72955
*/