need tsql help

Question

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

Thanks, 
Sam 

Answer 1

Hi @Samantha r

Try this:

SELECT [tran_log_writes]
      ,CAST(SUBSTRING([tran_log_writes],CHARINDEX('(',[tran_log_writes])+1,CHARINDEX('kB',[tran_log_writes])-CHARINDEX('(',[tran_log_writes])-1) AS int)/1024/1024 AS GB
FROM test2

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.