从字符串数据中找到数据返回
表内容如下
CARDID LINK O
705357942372 |4126|5855|8038|0
206891946170 |4126|5855|8038|0
073046259788 |4126|5855|8038|0
123506574234 |4126|5855|8038|0
我要返回 CARDID和对应的LINK里最后一个保存在|X|中的数据，返回内容应该如下
CARDID LINK
705357942372 8038
206891946170 8038
073046259788 8038
123506574234 8038
需要注意LINK不是定长，总长度和里面的数字长度都不是定长
[解决办法]
Declare @S Varchar(100)
Select @S = '|4126|5855|8038| '
Select Stuff(Left(@S, Len(@S) -1), 1, Len(@S) - CharIndex( '| ', REVERSE(@S), 2) + 1, ' ')
--Result
/*
8038
*/
[解决办法]
Select
CARDID,
Stuff(Left(LINK, Len(LINK) -1), 1, Len(LINK) - CharIndex( '| ', REVERSE(LINK), 2) + 1, ' ') As LINK
From
TableName
[解决办法]
declare @t table(CARDID varchar(20),LINK varchar(20),O int)
insert into @t select 705357942372, '|4126|5855|8038| ',0
insert into @t select 206891946170, '|4126|5855|8038| ',0
insert into @t select 073046259788, '|4126|5855|8038| ',0
insert into @t select 123506574234, '|4126|5855|8038| ',0
select
CARDID,reverse(left(LINK,charindex( '| ',LINK,1)-1)) as LINK
from
(select CARDID,reverse(left(LINK,len(LINK)-1)) as LINK from @t) t
/*
CARDID LINK
-------------------- --------------------
705357942372 8038
206891946170 8038
73046259788 8038
123506574234 8038
*/
[解决办法]
Create Table TEST
(CARDIDVarchar(20),
LINKVarchar(100),
OInt)
Insert TEST Select '705357942372 ', '|4126|5855|8038| ',0
Union All Select '206891946170 ', '|4126|5855|8038| ',0
Union All Select '073046259788 ', '|4126|5855|8038| ',0
Union All Select '123506574234 ', '|4126|5855|8038| ',0
GO
Select
CARDID,
Stuff(Left(LINK, Len(LINK) -1), 1, Len(LINK) - CharIndex( '| ', REVERSE(LINK), 2) + 1, ' ') As LINK
From
TEST
GO
Drop Table TEST
--Result
/*
CARDIDLINK
7053579423728038
2068919461708038
0730462597888038
1235065742348038
*/
