Use the PATINDEX function to check for an integer value continued in the string.
If PATINDEX returns a value greater than 0 then the string does contain an integer, you can use PATINDEX to determine the position of the integer within the string.
If PATINDEX is not greater than zero then the string does not contain an integer value.
DECLARE @string varchar(50)
SET @string = ‘this is a string with numb3rs in it’
IF PATINDEX(‘%[0-9]%’,@string) > 0
PRINT ‘YES, The string has contains the number ‘ +
‘ at position ‘ +
cast(PATINDEX(‘%[0-9]%’,@string) as char)
PRINT ‘NO, The string does not have numbers’