Hi All,
Need some TSQL help. Need to extract a portion of the string of [tran_log_writes] column, convert that value to GB and display it as seperate column as "TLOG-gen-GB".
Below is the sample data.
CREATE TABLE [dbo].[test2](
[tran_log_writes] [varchar](8000) NULL
)
GO
INSERT [dbo].[test2] ([tran_log_writes]) VALUES (N'db1: 245471085 (68491820 kB)')
GO
INSERT [dbo].[test2] ([tran_log_writes]) VALUES (N'db1: 245471085 (68491820 kB)')
GO
INSERT [dbo].[test2] ([tran_log_writes]) VALUES (N'db1: 245471085 (68491820 kB)')
GO
INSERT [dbo].[test2] ([tran_log_writes]) VALUES (N'db1: 245471085 (68491820 kB)')
GO
INSERT [dbo].[test2] ([tran_log_writes]) VALUES (N'db1: 245471085 (68491820 kB)')
GO
INSERT [dbo].[test2] ([tran_log_writes]) VALUES (N'db1: 245471085 (68491820 kB)')
GO
INSERT [dbo].[test2] ([tran_log_writes]) VALUES (N'db1: 245471085 (68491820 kB)')
GO
INSERT [dbo].[test2] ([tran_log_writes]) VALUES (N'db1: 245471085 (68491820 kB)')
GO
select * from test2
select 68491820/1024/1024 as GB
expected sample output
Thanks,
Sam
DROP TABLE IF EXISTS #test2;
CREATE TABLE #test2
(
tran_log_writes VARCHAR(8000) NULL
);
INSERT #test2
(
tran_log_writes
)
VALUES
(N'db1: 245471085 (68491820 kB)');
SELECT t.tran_log_writes
,[TLOG-gen-GB] = CAST (SUBSTRING (t.tran_log_writes, c.StartPos, c.EndPos - c.StartPos) AS INT) / (1024 * 1024)
FROM #test2 t
CROSS APPLY
(
SELECT StartPos = CHARINDEX ('(', t.tran_log_writes) + 1
,EndPos = CHARINDEX (' KB', t.tran_log_writes)
) c;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 25, 2023 at 2:13 pm
Thanks a ton Phil.
July 25, 2023 at 2:20 pm
An alternative that allows a previous open paren(s) in the input data:
DROP TABLE IF EXISTS #test2;
CREATE TABLE #test2 ( tran_log_writes nvarchar(4000) NULL );
INSERT #test2 ( tran_log_writes ) VALUES (N'db1: 245471085 (68491820 kB)'),
(N'db2:(retired) 245471 (1491820 kB)');
SELECT tran_log_writes,
CAST(SUBSTRING(tran_log_writes, end_byte - value_length, value_length) AS int) / 1024 / 1024 AS [TLOG-gen-GB]
FROM #test2
CROSS APPLY (
SELECT CHARINDEX(' kB', tran_log_writes) AS end_byte
) AS ca1
CROSS APPLY (
SELECT PATINDEX('%[^0-9]%', REVERSE(LEFT(tran_log_writes, end_byte - 1))) - 1 AS value_length
) AS ca2
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply