The selected answer does not work.

declare @str varchar(50)='79D136'

select 1 where @str NOT LIKE '%[^0-9]%'

I don't have a solution but know of this potential pitfall. The same goes if you substitute the letter 'D' for 'E' which is scientific notation.

ITS WORKS!!!

SELECT my_field FROM my_table WHERE REGEXP_LIKE(my_field, '[^0-9]') - only with char
 SELECT my_field FROM my_table WHERE REGEXP_LIKE(my_field, '[0-9]') - only numbers

Use Not Like

where some_column NOT LIKE '%[^0-9]%'

Demo

declare @str varchar(50)='50'--'asdarew345'

select 1 where @str NOT LIKE '%[^0-9]%'

There is a system function called ISNUMERIC for SQL 2008 and up. An example:

SELECT myCol
FROM mTable
WHERE ISNUMERIC(myCol)<> 1;

I did a couple of quick tests and also looked further into the docs:

ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0.

Which means it is fairly predictable for example

-9879210433 would pass but 987921-0433 does not. $9879210433 would pass but 9879210$433 does not.

So using this information you can weed out based on the list of valid currency symbols and + & - characters.

Solution: where some_column NOT LIKE '%[^0-9]%' Is correct.

Just one important note: Add validation for when the string column = '' (empty string). This scenario will return that '' is a valid number as well.

Method that will work. The way it is used above will not work.

declare @str varchar(50)='79136'

select 
  case 
    when  @str LIKE replicate('[0-9]',LEN(@str)) then 1 
    else 0 
  end

declare @str2 varchar(50)='79D136'

select 
  case 
    when  @str2 LIKE replicate('[0-9]',LEN(@str)) then 1 
    else 0 
  end

DECLARE @x int=1
declare @exit bit=1
WHILE @x<=len('123c') AND @exit=1
BEGIN
IF ascii(SUBSTRING('123c',@x,1)) BETWEEN 48 AND 57
BEGIN
set @x=@x+1
END
ELSE
BEGIN
SET @exit=0
PRINT 'string is not all numeric  -:('
END
END

I was attempting to find strings with numbers ONLY, no punctuation or anything else. I finally found an answer that would work here .

Using PATINDEX('%[^0-9]%', some_column) = 0 allowed me to filter out everything but actual number strings.