add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
(No column name)
Microsoft SQL Server 2014 (SP3-CU-GDR) (KB4535288) - 12.0.6372.1 (X64)
Dec 12 2019 15:14:11
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 17763: ) (Hypervisor)
8 rows affected
5 rows affected
(No column name)
0000925582
0000922221
0000925361
0000925461
0000935461
7463847412
8463847412
4870000925451
53434870000925451
433343434353434870000925451
51
5
(No column name)
0000925582
0000922221
0000925361
0000925461
0000935461
7463847412
8463847412
4870000925451
53434870000925451
433343434353434870000925451
51
5
null
(No column name)
2855290000
1222290000
1635290000
1645290000
1645390000
2147483647
2147483648
1545290000784
15452900007843435
154529000078434353434343334
15
5
(No column name)
null
1222290000
1635290000
1645290000
1645390000
2147483647
null
null
null
null
15
5
0
id data int_condition
5 {"name":"A Name ", "update_date":"1645390000"} 1645390000
6 {"name":"An Ordinary Name", "update_date":"2147483647"} 2147483647
id data (No column name) (No column name) (No column name) (No column name)
1 {"name":"Cole", "update_date":"2855290000"} 43 32 10 2855290000
2 {"name":"Peter", "update_date":"1222290000"} 44 33 10 1222290000
3 {"name":"A Very Long Silly Name", "update_date":"1635290000"} 61 50 10 1635290000
4 {"name":"A Long Name", "update_date":"1645290000"} 50 39 10 1645290000
5 {"name":"A Name ", "update_date":"1645390000"} 46 35 10 1645390000
6 {"name":"An Ordinary Name", "update_date":"2147483647"} 55 44 10 2147483647
7 {"name":"Some Other Name", "update_date":"2147483648"} 54 43 10 2147483648
8 {"name":"A Name Yet Again", "update_date":"1545290000784"} 58 44 13 1545290000784
9 {"name":"A Name Yet Again", "update_date":"15452900007843435"} 62 44 17 15452900007843435
10 {"name":"A Name Yet Again", "update_date":"154529000078434353434343334"} 72 44 27 null
11 {"name":"A Short String", "update_date":"15"} 45 42 2 15
12 {"name":"Extremely Short", "update_date":"5"} 45 43 1 5
13 {"name":"", "update_date":""} 29 28 0 0
id data (No column name) (No column name) (No column name) (No column name)
1 {"name":"Cole", "update_date":"2855290000"} 43 32 10 2855290000
5 {"name":"A Name ", "update_date":"1645390000"} 46 35 10 1645390000
6 {"name":"An Ordinary Name", "update_date":"2147483647"} 55 44 10 2147483647
7 {"name":"Some Other Name", "update_date":"2147483648"} 54 43 10 2147483648
8 {"name":"A Name Yet Again", "update_date":"1545290000784"} 58 44 13 1545290000784
9 {"name":"A Name Yet Again", "update_date":"15452900007843435"} 62 44 17 15452900007843435
id data
1 {"name":"Cole", "update_date":"2855290000"}
4 {"name":"A Long Name", "update_date":"1645290000"}
5 {"name":"A Name ", "update_date":"1645390000"}
6 {"name":"An Ordinary Name", "update_date":"2147483647"}
7 {"name":"Some Other Name", "update_date":"2147483648"}
8 {"name":"A Name Yet Again", "update_date":"1545290000784"}
9 {"name":"A Name Yet Again", "update_date":"15452900007843435"}
10 {"name":"A Name Yet Again", "update_date":"154529000078434353434343334"}
(No column name)
null
2008-09-24 21:00:00.000
2021-10-26 23:13:20.000
2022-02-19 17:00:00.000
2022-02-20 20:46:40.000
2038-01-19 03:14:07.000
null
null
null
null
1970-01-01 00:00:15.000
null
null
id data int_condition
5 {"name":"A Name ", "update_date":"1645390000"} 1645390000
6 {"name":"An Ordinary Name", "update_date":"2147483647"} 2147483647
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
2 1 SELECT
  id, data,
  TRY_CAST
  (
    REVERSE
    (
      SUBSTRING
      (
        REVERSE(data),
        CHARINDEX('"', REVERSE(data)) + 1,
        CHARINDEX('"', REVERSE(data), 5) - CHARINDEX('"', REVERSE(data)) -1
      )
    ) AS INT) AS int_condition
FROM test
WHERE
  TRY_CAST
  (
    REVERSE
    (
      SUBSTRING
      (
        REVERSE(data),
        CHARINDEX('"', REVERSE(data)) + 1,
        CHARINDEX('"', REVERSE(data), 5) - CHARINDEX('"', REVERSE(data)) -1
      )
    ) AS INT) > 1645290000
1 1 0 null null null null 3.9 null null null 0.00329903 null null SELECT False null
0 0   |--Compute Scalar(DEFINE:([Expr1003]=TRY_CAST(reverse(substring(reverse([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data]),charindex('"',reverse([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data]))+(1),charindex('"',reverse([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data]),(5))-charindex('"',reverse([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data]))-(1))) AS int))) 1 2 1 Compute Scalar Compute Scalar DEFINE:([Expr1003]=TRY_CAST(reverse(substring(reverse([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data]),charindex('"',reverse([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data]))+(1),charindex('"',reverse([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data]),(5))-charindex('"',reverse([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data]))-(1))) AS int)) [Expr1003]=TRY_CAST(reverse(substring(reverse([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data]),charindex('"',reverse([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data]))+(1),charindex('"',reverse([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data]),(5))-charindex('"',reverse([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data]))-(1))) AS int) 3.9 0 3.9E-07 4043 0.00329903 [fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[id], [fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data], [Expr1003] null PLAN_ROW False 1
2 1        |--Filter(WHERE:(TRY_CAST(reverse(substring(reverse([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data]),charindex('"',reverse([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data]))+(1),charindex('"',reverse([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data]),(5))-charindex('"',reverse([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data]))-(1))) AS int)>(1645290000))) 1 3 2 Filter Filter WHERE:(TRY_CAST(reverse(substring(reverse([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data]),charindex('"',reverse([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data]))+(1),charindex('"',reverse([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data]),(5))-charindex('"',reverse([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data]))-(1))) AS int)>(1645290000)) null 3.9 0 2.34E-06 4039 0.00329864 [fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[id], [fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data] null PLAN_ROW False 1
13 1             |--Table Scan(OBJECT:([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test])) 1 4 3 Table Scan Table Scan OBJECT:([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test]) [fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[id], [fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data] 13 0.0032035 9.28E-05 4039 0.0032963 [fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[id], [fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data] null PLAN_ROW False 1
Table 'test'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

id data
1 {"name":"Cole", "update_date":"2855290000"}
4 {"name":"A Long Name", "update_date":"1645290000"}
5 {"name":"A Name ", "update_date":"1645390000"}
6 {"name":"An Ordinary Name", "update_date":"2147483647"}
7 {"name":"Some Other Name", "update_date":"2147483648"}
8 {"name":"A Name Yet Again", "update_date":"1545290000784"}
9 {"name":"A Name Yet Again", "update_date":"15452900007843435"}
10 {"name":"A Name Yet Again", "update_date":"154529000078434353434343334"}
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
8 1 SELECT
    DTO.*
FROM dbo.test AS DTO
CROSS APPLY
(
    SELECT
        TRY_CONVERT(xml,
            REPLACE(
                REPLACE(
                    REPLACE(
                        REPLACE(DTO.[DATA],
                            '"name":', 'name='),
                        ', "update_date":', ' update_date='),
                    '{', '<r '),
                '}', '/>'))
) AS X (x)
WHERE
    1 = X.x.exist('r[1][@update_date ge 1645290000]')
1 1 0 null null null null 11.7 null null null 11.76689 null null SELECT False null
8 1   |--Nested Loops(Left Semi Join, OUTER REFERENCES:([DTO].[data])) 1 2 1 Nested Loops Left Semi Join OUTER REFERENCES:([DTO].[data]) null 11.7 0 4.8906E-05 90 11.76689 [DTO].[id], [DTO].[data] null PLAN_ROW False 1
13 1        |--Filter(WHERE:([Expr1013] IS NOT NULL)) 1 3 2 Filter Filter WHERE:([Expr1013] IS NOT NULL) null 11.7 0 6.24E-06 90 0.00330384 [DTO].[id], [DTO].[data] null PLAN_ROW False 1
0 0        | |--Compute Scalar(DEFINE:([Expr1013]=TRY_CAST(replace(replace(replace(replace([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data] as [DTO].[data],'"name":',CONVERT_IMPLICIT(varchar(max),'name=',0)),', "update_date":',CONVERT_IMPLICIT(varchar(max),' update_date=',0)),'{',CONVERT_IMPLICIT(varchar(max),'<r ',0)),'}',CONVERT_IMPLICIT(varchar(max),'/>',0)) AS xml))) 1 4 3 Compute Scalar Compute Scalar DEFINE:([Expr1013]=TRY_CAST(replace(replace(replace(replace([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data] as [DTO].[data],'"name":',CONVERT_IMPLICIT(varchar(max),'name=',0)),', "update_date":',CONVERT_IMPLICIT(varchar(max),' update_date=',0)),'{',CONVERT_IMPLICIT(varchar(max),'<r ',0)),'}',CONVERT_IMPLICIT(varchar(max),'/>',0)) AS xml)) [Expr1013]=TRY_CAST(replace(replace(replace(replace([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test].[data] as [DTO].[data],'"name":',CONVERT_IMPLICIT(varchar(max),'name=',0)),', "update_date":',CONVERT_IMPLICIT(varchar(max),' update_date=',0)),'{',CONVERT_IMPLICIT(varchar(max),'<r ',0)),'}',CONVERT_IMPLICIT(varchar(max),'/>',0)) AS xml) 13 0 1.3E-06 4116 0.0032976 [DTO].[id], [DTO].[data], [Expr1013] null PLAN_ROW False 1
13 1        | |--Table Scan(OBJECT:([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test] AS [DTO])) 1 5 4 Table Scan Table Scan OBJECT:([fiddle_d76e583f0e7f45388aa5fb196a96e617].[dbo].[test] AS [DTO]) [DTO].[id], [DTO].[data] 13 0.003125 0.0001713 90 0.0032963 [DTO].[id], [DTO].[data] null PLAN_ROW False 1
8 13        |--Nested Loops(Left Semi Join, OUTER REFERENCES:(XML Reader with XPath filter.[id], [Expr1015])) 1 10 2 Nested Loops Left Semi Join OUTER REFERENCES:(XML Reader with XPath filter.[id], [Expr1015]) null 1 0 4.18E-06 9 11.76354 null null PLAN_ROW False 11.7
13 13             |--Top(TOP EXPRESSION:((1))) 1 11 10 Top Top TOP EXPRESSION:((1)) null 1 0 1E-07 913 0.0587365 XML Reader with XPath filter.[id], [Expr1015] null PLAN_ROW False 11.7
0 0             | |--Compute Scalar(DEFINE:([Expr1007]=0x58, [Expr1015]=getdescendantlimit(XML Reader with XPath filter.[id]))) 1 12 11 Compute Scalar Compute Scalar DEFINE:([Expr1007]=0x58, [Expr1015]=getdescendantlimit(XML Reader with XPath filter.[id])) [Expr1007]=0x58, [Expr1015]=getdescendantlimit(XML Reader with XPath filter.[id]) 1 0 2E-05 1365 0.05873533 XML Reader with XPath filter.[id], [Expr1007], [Expr1015] null PLAN_ROW False 11.7
13 13             | |--Table-valued function 1 13 12 Table-valued function Table-valued function null null 1 0 1.004 461 0.05873416 XML Reader with XPath filter.[id] null PLAN_ROW False 11.7
8 13             |--Filter(WHERE:(XML Reader with XPath filter.[id]=getancestor(XML Reader with XPath filter.[id],(1)) AND xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XML Reader with XPath filter.[tid],(18),(75),(0)) IS NOT NULL AND xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XML Reader with XPath filter.[tid],(18),(75),(0))>=1.64529e+009)) 1 24 10 Filter Filter WHERE:(XML Reader with XPath filter.[id]=getancestor(XML Reader with XPath filter.[id],(1)) AND xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XML Reader with XPath filter.[tid],(18),(75),(0)) IS NOT NULL AND xsd_cast(XML Reader with XPath filter.[value],XML Reader with XPath filter.[lvalue],XML Reader with XPath filter.[tid],(18),(75),(0))>=1.64529e+009) null 1 0 4.644E-05 9 11.70475 null null PLAN_ROW False 11.7
13 13                  |--Table-valued function 1 25 24 Table-valued function Table-valued function null null 18 0 1.00036 8501 11.70421 XML Reader with XPath filter.[id], XML Reader with XPath filter.[tid], XML Reader with XPath filter.[value], XML Reader with XPath filter.[lvalue] null PLAN_ROW False 11.7
Table 'test'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.