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