SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

declare @Samples as Table ( Sample VarChar(80) );
insert into @Samples ( Sample ) values
  ( 'SAND FRACING 100 MESH (1441317)' ),
  ( 'SANDFRAC4070 (1441316)' ),
  ( 'SANDFRAC4070(1441316)' ),
  ( 'SANDFRAC3050MES' ),
  ( 'SAND FRACING 30 50 MESH' ),
  ( 'SAND FRACING (30) 50 MESH' ),
  ( 'SAND FRACING (30) (50) MESH' ),
  ( 'SANDFRAC4070 (1441)(316)' ),
  ( 'SANDFRAC4070 (1441) (316)' ),
  ( 'SANDWICH CHICKEN (NO ONION)' ),
  ( 'SAND ()' ),
  ( 'SAND (0)' ),
  ( 'SAND (-0)' ),
  ( 'SAND (6.022E+23)' ),
  ( 'SAND (e)' ),
  ( '' ),
  ( NULL );

with
  Step1 as (
    select Sample,
      -- Find the last open parenthesis by searching backwards from the end of the string.
      Len( Sample ) - CharIndex( '( ', Reverse( Sample ) ) as LastOpenParen
      from @Samples ),
  Step2 as (
    select Sample, LastOpenParen,
      case
        -- If there was no open parenthesis, or no string, then we're done.
        when LastOpenParen = Len( Sample ) or LastOpenParen is NULL then NULL
        -- The target substring runs from the last open parenthesis to the end of the string.
        else Substring( Sample, LastOpenParen + 1, 80 ) end as Target
      from Step1 )
  select Sample, Len( Sample ) as Length, LastOpenParen,
    case
      -- Nothing interesting.
      when Target is NULL then Sample
      -- A string in parentheses, but including a non-digit character.
      when Target like '(%[^0-9]%)' then Sample
      -- A string in parentheses with at least one digit and no non-digits.
      when Target like '(%[0-9]%)' then RTrim( Left( Sample, LastOpenParen ) )
      -- Empty parenteses.
      else Sample end as CorrectedSample
    from Step2;

Sample	Length	LastOpenParen	CorrectedSample
SAND FRACING 100 MESH (1441317)	31	22	SAND FRACING 100 MESH
SANDFRAC4070 (1441316)	22	13	SANDFRAC4070
SANDFRAC4070(1441316)	21	21	SANDFRAC4070(1441316)
SANDFRAC3050MES	15	15	SANDFRAC3050MES
SAND FRACING 30 50 MESH	23	23	SAND FRACING 30 50 MESH
SAND FRACING (30) 50 MESH	25	13	SAND FRACING (30) 50 MESH
SAND FRACING (30) (50) MESH	27	18	SAND FRACING (30) (50) MESH
SANDFRAC4070 (1441)(316)	24	13	SANDFRAC4070 (1441)(316)
SANDFRAC4070 (1441) (316)	25	20	SANDFRAC4070 (1441)
SANDWICH CHICKEN (NO ONION)	27	17	SANDWICH CHICKEN (NO ONION)
SAND ()	7	5	SAND ()
SAND (0)	8	5	SAND
SAND (-0)	9	5	SAND (-0)
SAND (6.022E+23)	16	5	SAND (6.022E+23)
SAND (e)	8	5	SAND (e)
	0	0
null	null	null	null