1

I've inherited a table in a third part app that contains both strings and ints - I want to split the table but want to know the fastest way to determine if the value is an int? Column is varchar(250)

e.g.
Save
123456
Edit
Save
123455
Delete
123444
BlueChippy
  • 5,935
  • 16
  • 81
  • 131
  • 1
    @TabAlleman I'd suggest that's a poor duplicate. Most of those answers recommend the use of `ISNUMERIC`, which is an awful function in truth (take `SELECT CASE WHEN ISNUMERIC('1.1') = 1 THEN CONVERT(int, '1.1') END;` for example). `TRY_CONVERT` is a far better option than those mentioned there. – Thom A Mar 07 '19 at 17:01
  • I know that a lot of the answers on that question are for 2008 functionality, but there is one answer that mentions using TRY_CONVERT() with 2012+. Seems to me that the SO thing to do is to add upvotes to that answer in the duplicate question, rather than add this duplicate to the site, which doesn't contribute anything new in the question itself. – Tab Alleman Mar 07 '19 at 19:19

3 Answers3

6

I would suggest using TRY_CONVERT, which will return NULL if the value cannot be converted:

SELECT TRY_CONVERT(int, YourColumn) AS intColumn
FROM YourTable
--WHERE TRY_CONVERT(int, YourColumn) IS NOT NULL --If you only want rows that converted.

If you prefer the syntax, you also have TRY_CAST (TRY_CAST(YourColumn AS int)).

Thom A
  • 88,727
  • 11
  • 45
  • 75
5

I would use TRY_CONVERT.

declare @Something table(SomeVal varchar(50))

insert @Something values
('Save')
, ('123456')
, ('Edit')
, ('Save')
, ('123455')
, ('Delete')
, ('123444')

select *
from @Something s
where TRY_CONVERT(int, s.SomeVal) is not null
Sean Lange
  • 33,028
  • 3
  • 25
  • 40
4

One approach would be to use SQL Server's enhanced LIKE operator:

SELECT col
FROM yourTable
WHERE col NOT LIKE '%[^0-9]%';

Using TRY_CONVERT might also be an option, but only if you are using a more recent version of SQL Server.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360