February 18, 2020 at 11:12 am
hi,
How can I extract part of this string
declare @string varchar(1024)
set @string = 'Total # of bytes : 128270200832 (119.46GB)'
I only need the numbers after : and without the (119.46GB)
select Substring (@string, Charindex( ':', @string )+2 , Len(@string))
THX
February 18, 2020 at 11:29 am
Hi,
Can you try:
declare @string varchar(1024)
set @string = 'Total # of bytes : 128270200832 (119.46GB)'
SELECT SUBSTRING(SUBSTRING(@string, CHARINDEX(':', @string) + 1, LEN(@string))
, 1
,CHARINDEX('(', SUBSTRING(@string, CHARINDEX(':', @string) + 1, LEN(@string)))-1);
February 18, 2020 at 12:38 pm
FYI, Cross post on Stack Overflow. not sure why The OP posted this here, when they already had 2 answers on there...
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 18, 2020 at 1:06 pm
thx it's working good.
February 18, 2020 at 1:36 pm
Just for fun, this can be done with four functions
😎
USE TEEST;
GO
SET NOCOUNT ON;
DECLARE @string varchar(1024) = 'Total # of bytes : 128270200832 (119.46GB)';
;WITH NO_SPACE AS
(
SELECT STUFF(REPLACE(@string,' ',''),1,14,'') AS NSPSTR
)
SELECT
LEFT(NS.NSPSTR,CHARINDEX('(',NS.NSPSTR,1) - 1)
FROM NO_SPACE NS;
February 18, 2020 at 3:29 pm
To allow for some variations in the format:
SELECT string, numbers
FROM ( VALUES
('Total # of bytes : 128270200832 (119.46GB)'),
('Total # of bytes : 128270200832(119.46GB)'),
('Total # of bytes : 128270200832 119.46GB'),
('Total # of bytes : 128270200832'),
('Total # of bytes : unavailable')
) AS strings(string)
CROSS APPLY (
SELECT CHARINDEX(':', string + ':') + 2 AS numbers_begin
) AS ca1
CROSS APPLY (
SELECT SUBSTRING(string, numbers_begin, PATINDEX('%[^0-9]%',
SUBSTRING(string, numbers_begin, 1024) + '/') - 1) as numbers
) 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".
February 18, 2020 at 4:30 pm
Less than four functions, anyone?
SELECT string, numbers
FROM ( VALUES
('Total # of bytes : 128270200832 (119.46GB)'),
('Total # of bytes : 128270200832(119.46GB)'),
('Total # of bytes : 128270200832 119.46GB'),
('Total # of bytes : 128270200832'),
('Total # of bytes : unavailable')
) AS strings(string)
CROSS APPLY (SELECT CHARINDEX(':', string + ':') + 2 AS numbers_begin) AS ca1
CROSS APPLY (SELECT SUBSTRING(string, numbers_begin, PATINDEX('%[^0-9]%',
SUBSTRING(string, numbers_begin, 1024) + '/') - 1) as numbers
) AS ca2
/*
[Expr1007] = Scalar Operator(substring([Union1005],charindex(':',[Union1005]+':')+(2),patindex('%[^0-9]%',substring([Union1005],charindex(':',[Union1005]+':')+(2),(1024))+'/')-(1))) -- 5 functiona
*/
SELECT string, numbers
FROM ( VALUES
('Total # of bytes : 128270200832 (119.46GB)'),
('Total # of bytes : 128270200832(119.46GB)'),
('Total # of bytes : 128270200832 119.46GB'),
('Total # of bytes : 128270200832'),
('Total # of bytes : unavailable')
) AS strings(string)
CROSS APPLY (SELECT RightBit = REPLACE(string,'Total # of bytes : ','')) x1
CROSS APPLY (SELECT Numbers = LEFT(RightBit,PATINDEX('%[^0-9]%',RightBit + '/') -1 )) y
/*
[Expr1007] = Scalar Operator(substring(replace([Union1005],'Total # of bytes : ',''),(1),patindex('%[^0-9]%',replace([Union1005],'Total # of bytes : ','')+'/')-(1))) -- 4 functions
*/
SELECT string, numbers
FROM ( VALUES
('Total # of bytes : 128270200832 (119.46GB)'),
('Total # of bytes : 128270200832(119.46GB)'),
('Total # of bytes : 128270200832 119.46GB'),
('Total # of bytes : 128270200832'),
('Total # of bytes : unavailable')
) AS strings(string)
CROSS APPLY (SELECT NSPSTR = STUFF(REPLACE(string,' ',''),1,14,'') ) ns
CROSS APPLY (SELECT Numbers = LEFT(NS.NSPSTR,CHARINDEX('(',NS.NSPSTR,1) - 1)) y
/*
[Expr1007] = Scalar Operator(substring(stuff(replace([Union1005],' ',''),(1),(14),''),(1),charindex('(',stuff(replace([Union1005],' ',''),(1),(14),''),(1))-(1))) -- 6 functions
*/
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 18, 2020 at 5:13 pm
Less than four functions, anyone?
SELECT string, numbers
FROM ( VALUES
('Total # of bytes : 128270200832 (119.46GB)'),
('Total # of bytes : 128270200832(119.46GB)'),
('Total # of bytes : 128270200832 119.46GB'),
('Total # of bytes : 128270200832'),
('Total # of bytes : unavailable')
) AS strings(string)
CROSS APPLY (SELECT CHARINDEX(':', string + ':') + 2 AS numbers_begin) AS ca1
CROSS APPLY (SELECT SUBSTRING(string, numbers_begin, PATINDEX('%[^0-9]%',
SUBSTRING(string, numbers_begin, 1024) + '/') - 1) as numbers
) AS ca2
/*
[Expr1007] = Scalar Operator(substring([Union1005],charindex(':',[Union1005]+':')+(2),patindex('%[^0-9]%',substring([Union1005],charindex(':',[Union1005]+':')+(2),(1024))+'/')-(1))) -- 5 functiona
*/
SELECT string, numbers
FROM ( VALUES
('Total # of bytes : 128270200832 (119.46GB)'),
('Total # of bytes : 128270200832(119.46GB)'),
('Total # of bytes : 128270200832 119.46GB'),
('Total # of bytes : 128270200832'),
('Total # of bytes : unavailable')
) AS strings(string)
CROSS APPLY (SELECT RightBit = REPLACE(string,'Total # of bytes : ','')) x1
CROSS APPLY (SELECT Numbers = LEFT(RightBit,PATINDEX('%[^0-9]%',RightBit + '/') -1 )) y
/*
[Expr1007] = Scalar Operator(substring(replace([Union1005],'Total # of bytes : ',''),(1),patindex('%[^0-9]%',replace([Union1005],'Total # of bytes : ','')+'/')-(1))) -- 4 functions
*/
SELECT string, numbers
FROM ( VALUES
('Total # of bytes : 128270200832 (119.46GB)'),
('Total # of bytes : 128270200832(119.46GB)'),
('Total # of bytes : 128270200832 119.46GB'),
('Total # of bytes : 128270200832'),
('Total # of bytes : unavailable')
) AS strings(string)
CROSS APPLY (SELECT NSPSTR = STUFF(REPLACE(string,' ',''),1,14,'') ) ns
CROSS APPLY (SELECT Numbers = LEFT(NS.NSPSTR,CHARINDEX('(',NS.NSPSTR,1) - 1)) y
/*
[Expr1007] = Scalar Operator(substring(stuff(replace([Union1005],' ',''),(1),(14),''),(1),charindex('(',stuff(replace([Union1005],' ',''),(1),(14),''),(1))-(1))) -- 6 functions
*/
I don't like to have too much dependency on the specifics of the way the data happens to look now, i.e., forcing the starting string to remain exactly the same and/or a specific char(s) to appear after the data.
Thus, my idea was to have more flexible support for variations in the format, not, for me personally, solely on reducing the number of functions used, although efficiency was also a consideration, of course.
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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply