WHERE
   SUBSTRING(Name,5,3) NOT LIKE '%[^0-9]%'

WHERE
   ISNUMERIC(SUBSTRING(Name,5,3)) = 1

quote:

---

Originally posted by sunitabeck

You can add a where clause. If it is all positive integers (i.e., you want to look for only digits 0 through 9), then you can do this:

WHERE
   SUBSTRING(Name,5,3) NOT LIKE '%[^0-9]%'

Or if you have negative numbers also use '%[^0-9-]%', decimal points, then'%[^0-9-.]%' etc.

Another way is to use the isnumeric function.

WHERE
   ISNUMERIC(SUBSTRING(Name,5,3)) = 1

---

quote:

---

ISNUMERIC is not fully reliable

---

SELECT CAST('$' AS MONEY);
SELECT CAST('-' AS INT);
SELECT CAST('1d0' AS FLOAT);
SELECT CAST (' ' AS INT);
SELECT CAST('2e2' AS FLOAT);

quote:

---

Originally posted by sunitabeck

quote:

---

ISNUMERIC is not fully reliable

---

I would prefer to call it GENEROUS rather than unreliable. Everything that returns a 1 for ISNUMERIC (such as $,-,1e0,2d2,space character etc.) can be converted to at least one numeric type. So SQL Server is being generous/liberal in considering what is numeric.

SELECT CAST('$' AS MONEY);
SELECT CAST('-' AS INT);
SELECT CAST('1d0' AS FLOAT);
SELECT CAST (' ' AS INT);
SELECT CAST('2e2' AS FLOAT);

---