What is correct like pattern for number

Question

I'm looking for correct pattern using like Operator.

My Code:

DECLARE @exp1 varchar(100) = '<<PointBalance , 8>>'
DECLARE @exp2 varchar(100) = '<<PointBalance , 985>>'    
IF (TRIM(REPLACE(@exp1, ' ', '')) LIKE '<<PointBalance,[0-9]>>')
    PRINT 'Matched'

As expected the if statement does not print 'Matched' for exp2.

Count of digits are not same. I need a pattern that validate are nDigit numbers.

Answer 1

Like patterns can't check for a variable length string of digits, but it can check a substring for any character that is not a digit:

-- Sample data.
declare @Samples as Table ( Sample VarChar(32) );
insert into @Samples ( Sample ) values
  ( '<<PointBalance , 1>>' ), ( '<<PointBalance , 12>>' ), ( '<<PointBalance , 123>>' ),
  ( '<<PointBalance , 1234>>' ), ( '<<PointBalance , 1 3 5>>' ), ( '<<PointBalance , 1J3>>' );

with
  Step1 as (
    select Sample,
      -- Extract the substring starting after the comma and trim leading whitespace.
      LTrim( Substring( Sample, CharIndex( ',', Sample ) + 1, 32 ) ) as AfterComma
      from @Samples ),
  Step2 as (
    select Sample, AfterComma,
      -- Extract the substring prior to the first   '>'   and trim trailing whitespace.
      RTrim( Substring( AfterComma, 1, CharIndex( '>', AfterComma ) - 1 ) ) as TargetString
      from Step1 )
select *,
  -- Check the remaining string for any characters that are not digits.
  case when TargetString like '%[^0-9]%' then 0 else 1 end as Numeric
  from Step2;