I want to return all rows where columnX has 10 digits of numerics and nothing else (e.g. '1234567890'). The column in question has other formatted data such as 4 alphas and 4 numerics (e.g. ABCD1234), etc.
I want to utilize my regex in SQL Server 2012 queries.
SQL Server REGEX uses the LIKE clause with % but for your case you can do this:
SELECT columX
FROM TableT
WHERE ISNUMERIC(columnX) = 1 and LEN(C1) = 10
LIKE is the closest thing you'll get to REGEX without a lengthy CASE statement or something similar. LIKE can use some REGEX type characters such as _ % [] and ^ but you can't use it like you can other languages such as Java. So generally speaking you can so "No Regex in SQL Server" as you did.There is no regexp in sql server.
You can use try_cast which returns null when there are any alphabets in the column when you cast it to an bigint.
select * from tablename
where try_cast(columnX as bigint) is not null
Edit: To check if the column length = 10 add one more condition to the where clause.
select * from tablename
where try_cast(columnX as bigint) is not null and len(columnX) = 10
