CODEWITHSUNDEEP
sql-server
Avi
Avi

Reputation: 43

Extract part of string in multiple places

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)

Upvotes: 0

Views: 104

Answers (3)

Red Devil
Red Devil

Reputation: 2403

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))

Upvotes: 0

Thom A
Thom A

Reputation: 96003

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

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

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

Upvotes: 1

Related Questions