用substring取数据长度的问题

[野鼻孔]

各位大神，今天碰到个问题，

我需要取record_key列的数据进行处理
Reassign application '121204' to user 'test'
Reassign application '121204' to user 'test2'
Reassign application '1000119031' to user 'user5'
Reassign application '1000119031' to user 'user10'
第一个双引号里是‘申请号’，我想把相同的两条取出来比较其他字段，但是必须忽略后面的 to user 'test',
因此我想了个方法，用substring(record_key, 23, 35)

121204' to user 'test'
121204' to user 'test2'
1000119031' to user 'user5'
1000119031' to user 'user10'

如何只取到上面第一个冒号结束，既 121204' 这里的冒号
用charindex行不行，
求具体代码，谢谢。

[AcHerat]

substring(record_key, charindex('''',record_key)+1, charindex(''' to',record_key) - charindex('''',record_key) - 1 ) 不确定是否可行，手打！

[octwind]

select stuff(replace(record_key,'Reassign application ''',''),charindex('''',replace(record_key,'Reassign application ''','')),len(replace(record_key,'Reassign application ''','')),'') from 表

[Ginnnnnnnn]

如果你是写死23的可以直接用

;WITH CTE (St) AS
(
SELECT 'Reassign application ''121204'' to user ''test'''
UNION ALL
SELECT 'Reassign application ''121204'' to user ''test2'''
UNION ALL
SELECT 'Reassign application ''1000119031'' to user ''user5'''
UNION ALL
SELECT 'Reassign application ''1000119031'' to user ''user10'''
)
SELECT Substring(St,23,CHARINDEX('''',St,24)-24)
	FROM CTE a

如果不是写死23的改成这样

;WITH CTE (St) AS
(
SELECT 'Reassign application ''121204'' to user ''test'''
UNION ALL
SELECT 'Reassign application ''121204'' to user ''test2'''
UNION ALL
SELECT 'Reassign application ''1000119031'' to user ''user5'''
UNION ALL
SELECT 'Reassign application ''1000119031'' to user ''user10'''
)
SELECT Substring(St,CHARINDEX('''',a.St)+1,CHARINDEX('''',St,CHARINDEX('''',a.St)+1)-CHARINDEX('''',a.St)-1)
	FROM CTE a

[中国风]

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([record_key] nvarchar(70))
Insert #T
select N'Reassign APPLICATION ''121204'' TO USER ''test''' union all
select N'Reassign APPLICATION ''121204'' TO USER ''test2''' union all
select N'Reassign APPLICATION ''1000119031'' TO USER ''user5''' union all
select N'Reassign APPLICATION ''1000119031'' TO USER ''user10'''
Go
Select SUBSTRING([record_key],CHARINDEX('''',[record_key])+1,CHARINDEX('''',[record_key],CHARINDEX('''',[record_key])+1)-CHARINDEX('''',[record_key])-1) from #T
/*
121204
121204
1000119031
1000119031
*/

[道素]

你也可以这样： PATINDEX('%''[0-9]%',@s) 找到第一单引号开头后边是数字的位置 PATINDEX('%[0-9]''[^0-9]%',@s) --找到数字后为单引号再后面为字母的位置

 DECLARE @s VARCHAR(500)='Reassign application ''121204'' to user test'''
 SELECT SUBSTRING(@s,PATINDEX('%''[0-9]%',@s)+1,PATINDEX('%[0-9]''[^0-9]%',@s)-PATINDEX('%''[0-9]%',@s))

返回：121204