How can I extract part of this string
declare @string varchar(1024)
set @string = 'Total # of bytes : 128270200832 (119.46GB)'
select Substring (@string, Charindex( ':', @string )+2 , Len(@string))
I only need the numbers after: and without the (119.46GB)
Use replace() :
select replace(replace(Substring (@string, Charindex( ':', @string )+2 , Len(@string)), '(', ''), ')', '');
EDIT : If you don't want values inside () then you can do :
select left(col, charindex('(', col) - 1)
from ( values (Substring (@string, Charindex( ':', @string )+2 , Len(@string)))
) t(col);
Seems like STUFF with CHARINDEX and a couple of replaces does the job:
DECLARE @string varchar(1024);
SET @string = 'Total # of bytes : 128270200832 (119.46GB)';
SELECT REPLACE(REPLACE(STUFF(@string,1,CHARINDEX(':',@string)+1,''),'(',''),')','');
Edit: Based on the new logic you have defined:
SELECT SUBSTRING(@String,V.CI,CHARINDEX('(',@String,V.CI) - V.CI)
FROM (VALUES(CHARINDEX(':',@string)+1))V(CI);
() characters. You should update your question to be clear.Try this:
declare @string varchar(1024)
set @string = 'Total # of bytes : 128270200832 (119.46GB)'
select substring(@string,charindex(':',@string) + 2,(charindex('(',@string) - charindex(':',@string)-2))
