Change varchar to int

Question

I have a senario i have no idea of how i can solve.

Well

I have database like this example myTable

ID(int) | Category (varchar(20)) 1 | 1029 2 | 2020 3 | 2030

All records in Category are numbers but the format is varchar. I can't change the type from varchar to int.

What i need is in my SQL query convert varchar to int of possible. Because, i need to do something likes tis

SELECT * FROM myTable HERE Category => 1000 AND Category =< 2000

How is that possible?

Sorry my written english, but i hope you understand anyway :)

Siyual · Accepted Answer · 2017-08-01 17:23:47Z

Just use a CONVERT:

SELECT * FROM myTable WHERE Convert(Int, Category) >= 1000 AND Convert(Int, Category) <= 2000

You could also simplify the query with a BETWEEN:

SELECT * FROM myTable WHERE Convert(Int, Category) BETWEEN 1000 AND 2000

Gordon Linoff · Accepted Answer · 2017-08-01 17:51:27Z

If you wanted to use an index, I would suggest:

SELECT * FROM myTable WHERE Category >= '1000' AND Category <= '2000' AND LEN(Category) = 4;

(The first two conditions can use the index.)

Admittedly, the length condition is rather specific to the values you are comparing to.

I would strongly encourage you to change your data structure. If you cannot, you can still use a computed column:

alter table mytable add category_int as (try_convert(int, category)) persisted;

You can then add an index on the computed column:

create index idx_mytable_category_int on mytable(category_int);

Then the formulation:

SELECT * FROM myTable WHERE Category_int >= 1000 AND Category_int <= 2000

Should work fine.

2 Answers 2