SQL Server doesn't have as good pattern matching abilities as regular expressions. You can search for the pattern:

[/\\][0-9]%[/\\]

That is, slash followed by a digit followed by any other string followed by a slash. This will match any characters after the first digit, but your examples have nothing of the form /1abc/.

If this is sufficient, then this does the trick:

select v.*, 
       left(v2.str2, patindex('%[/\\]%', v2.str2) - 1)
from (values ('\\servername\folder1\FTP\folder2\512/862450_FileBundle.zip')) v(str) cross apply
     (values (stuff(v.str, 1, patindex('%[/\\][0-9]%[/\\]%', v.str), ''))) v2(str2)

You'd use REGEXP_REPLACE in order to remove all non-digit characters from a string:

select regexp_replace(column_name, '[^0-9]', '')
from mytable;

or

select regexp_replace(column_name, '[^[:digit:]]', '')
from mytable;

Of course you can write a function extract_number. It seems a bit like overkill though, to write a funtion that consists of only one function call itself.

create function extract_number(in_number varchar2) return varchar2 is
begin
  return regexp_replace(in_number, '[^[:digit:]]', '');
end; 

Something like this might be helpful:

select PRODUTC_DESCRIPTION, 
TRIM( regexp_substr( PRODUTC_DESCRIPTION, '([\\.\\d ]*\\d+ *)((KG|K|GM))',1,1,'e')) as packsize_num,
TRIM( regexp_substr( PRODUTC_DESCRIPTION, '[\\.\\d ]*\\d+ *((KG|K|GM))',1,1,'e')) as packsize_unit,
IFF( packsize_unit IN ('K','KG'),  packsize_num * 1000, packsize_num  ) packsize
from values 
('PRODUCT A 3 CHEESE SLICE 170 GM'),
('PRODUCT B SUGAR 1.3KG (CL)'),
('PRODUCT C CHEESE SLICES 12X156GM'),
('PRODUCT KETCHUP BOTTLE 200GM (CL)'),
('PRODUCT KETCHUP 1.3KG (CL)') tmp(PRODUTC_DESCRIPTION);

+-----------------------------------+--------------+---------------+----------+
|        PRODUTC_DESCRIPTION        | PACKSIZE_NUM | PACKSIZE_UNIT | PACKSIZE |
+-----------------------------------+--------------+---------------+----------+
| PRODUCT A 3 CHEESE SLICE 170 GM   |          170 | GM            |      170 |
| PRODUCT B SUGAR 1.3KG (CL)        |          1.3 | KG            |    1,300 |
| PRODUCT C CHEESE SLICES 12X156GM  |          156 | GM            |      156 |
| PRODUCT KETCHUP BOTTLE 200GM (CL) |          200 | GM            |      200 |
| PRODUCT KETCHUP 1.3KG (CL)        |          1.3 | KG            |    1,300 |
+-----------------------------------+--------------+---------------+----------+

You can use a regular expression and a correlated hierarchical query to get the values:

Oracle Setup:

CREATE TABLE table_name ( id, value ) AS
SELECT 1, '[ 6d (1.5h; 31h)] x (5 to 7)' FROM DUAL UNION ALL
SELECT 2, '[ 1d (8h; 24h; 48.5h; 72h; 96h)] x (1 to 5)' FROM DUAL;

Query:

SELECT id, COLUMN_VALUE
FROM   table_name t
       CROSS JOIN
       TABLE(
         CAST(
           MULTISET(
             SELECT REGEXP_SUBSTR( t.value, '\d+\.?\d*', 1, LEVEL )
             FROM   DUAL
             CONNECT BY LEVEL <= REGEXP_COUNT( t.value, '\d+\.?\d*' )
           ) AS SYS.ODCINUMBERLIST
         )
       ) n;

Results:

ID  COLUMN_VALUE
--- ------------
1   6
1   1.5
1   31
1   5
1   7
2   1
2   8
2   24
2   48.5
2   72
2   96
2   1
2   5

Query 2:

SELECT REGEXP_SUBSTR( value, '\[\s*([0-9.]+)d\s*\((.*?)\)\]\s*x\s*\(([0-9.]+)\s*to\s*([0-9.]+)\)', 1, 1, NULL, 1 ) AS days,
       REGEXP_REPLACE(
         REGEXP_SUBSTR( value, '\[\s*([0-9.]+)d\s*\((.*?)\)\]\s*x\s*\(([0-9.]+)\s*to\s*([0-9.]+)\)', 1, 1, NULL, 2 ),
         '[^0-9.;]'
       )AS hours,
       REGEXP_SUBSTR( value, '\[\s*([0-9.]+)d\s*\((.*?)\)\]\s*x\s*\(([0-9.]+)\s*to\s*([0-9.]+)\)', 1, 1, NULL, 3 ) AS x_from,
       REGEXP_SUBSTR( value, '\[\s*([0-9.]+)d\s*\((.*?)\)\]\s*x\s*\(([0-9.]+)\s*to\s*([0-9.]+)\)', 1, 1, NULL, 4 ) AS x_to
FROM   table_name t;

Results:

DAYS HOURS           X_FROM X_TO
---- --------------- ------ ----
   6          1.5;31      5    7
   1 8;24;48.5;72;96      1    5

First create this UDF

CREATE FUNCTION dbo.udf_GetNumeric
(
  @strAlphaNumeric VARCHAR(256)
)
RETURNS VARCHAR(256)
AS
BEGIN
  DECLARE @intAlpha INT
  SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
  BEGIN
    WHILE @intAlpha > 0
    BEGIN
      SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
      SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
    END
  END
  RETURN LEN(COALESCE(TRIM(CAST(ISNULL(@strAlphaNumeric, 0) AS INT)),0))>0 then COALESCE(TRIM(CAST(ISNULL(@strAlphaNumeric, 0) AS INT)),0) else 0 end
END
GO

Now use the function as

SELECT dbo.udf_GetNumeric(column_name) 
from table_name

SQL FIDDLE

I hope this solved your problem.

Reference

4/10/23 - Modified Return Statement based on comments

Here's the example with PATINDEX:

select SUBSTRING(fieldName, PATINDEX('%[0-9]%', fieldName), LEN(fieldName))

This assumes (1) the field WILL have a numeric, (2) the numerics are all grouped together, and (3) the numerics don't have any subsequent characters after them.

Something like this, may be:

with t (input) as (select 'BO2003056-2')
select 
 replace(
  translate(
   input,
   'ABCDEFGHIJKLMNOPQRSTUVXYZ-',
   '                          '
  ),
  ' ', ''
 ) 
from t

You'll have to test for yourself whether this is "the fastest", but it's probably the simplest method.

Note that in the SQL Server version of TRANSLATE() the third argument (the string of replacement characters) must be of the same length as the second argument (the characters to be replaced).

You can avoid having to count all the characters you want to remove:

with t (input, unwanted) as (
  select 'BO2003056-2', 'ABCDEFGHIJKLMNOPQRSTUVXYZ-'
)
select 
 replace(
  translate(
   input,
   unwanted,
   replicate(' ', len(unwanted))
  ),
  ' ', ''
 ) 
from t

In other DBMSes the third argument can be shorter, and the characters for which there are no replacements will simply be deleted, making this approach even less complicated. For example, in Postgres:

with t (s) as (select 'BO2003056-2')
select 
 translate(s, 'ABCDEFGHIJKLMNOPQRSTUVXYZ-', '')
from t