Comparing/calculating VARCHARS that look like integers.

What is the best way to compare VARCHARS that look like INT?

I.e. compare 200000 to 300000 (are they the same)

Also - what is the best way to calculate with VARCHAR datatypes such as 200000 and 300000?
IT GuyDir of ITAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
You can compare them normally or convert them to big int   CAST(varcharColumn as Bigint)  and compare
0
Russ SuterCommented:
If you want to know if two VARCHARS are equal you can just simply set that in the WHERE clause
SELECT
*
FROM
MyTable
WHERE
'20000' = '30000'

Open in new window

That will never return ANY rows since the WHERE clause is evaluating two constant VARCHAR values that are not equal but you get the idea.
If you're doing calculations then VARCHAR is absolutely, positively, unequivocally the wrong data type to use for your column. You should never perform numeric calculations on text fields. That being said, you can do it by using CAST([columnname] AS INTEGER). Feel free to substitute INTEGER for the appropriate data type as needed. WARNING, performing such calculations on casted values will be sssloooowww!
0
SujithData ArchitectCommented:
>> I.e. compare 200000 to 300000 (are they the same)

wont a textual comparison be enough if you are looking if they are the same?

Unless you are looking for greater than or less then semantics; you should be able to compare them directly.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

IT GuyDir of ITAuthor Commented:
Sujith - I am looking for greater than and less than semantics.
0
Russ SuterCommented:
If you need greater than / less than then you'll need to use CAST([columname] as INTEGER) or the equivalent TRY_CAST() method. These are not the sort of things you should be doing on text fields.
1
SujithData ArchitectCommented:
How sure are you that the columns you are referring to always contains Numeric values?
You need to check for valid numbers if you are going to do such comparisons

like the below will give you "Lesser" as output -
SELECT 	case when ISNUMERIC('1234') = 1 and isnumeric('3456') = 1 
		then 
        	case when '1234' > '3456' then 'greater' else 'lesser' end
        else 'non numeric'
end;

Open in new window


whereas the below will say "non numeric"
SELECT 	case when ISNUMERIC('12a34') = 1 and isnumeric('3456') = 1 
		then 
        	case when '1234' > '3456' then 'greater' else 'lesser' end
        else 'non numeric'
end;

Open in new window

0
Mark WillsTopic AdvisorCommented:
What version of SQL Server ?

Since 2012 you have try_convert(), try_cast(), try_parse()

If comparing, then you have to think of strings as a string comparison where 200 is greater than 100000

Otherwise, and for mathematical work, you would have to first cast as a numeric data type.

You can do : case when isnumeric(your_column) = 1 then cast(your_column as int) else 0 end
or : try_cast(your_column as int)

have a read of try_cast : https://docs.microsoft.com/en-us/sql/t-sql/functions/try-cast-transact-sql?view=sql-server-2017
1
PortletPaulfreelancerCommented:
what is the best way to calculate with VARCHAR datatypes such as 200000 and 300000?
Assuming "calculate" means things like addition and subtraction then: The "best way" is to convert them to integers.

Also, whilst it is "reasonable" to compare strings of digits if they are the same overall length, but as soon as you expect numeric behaviour even if strings are of different lengths than you have an instant headache as I attempt o show below where '100' is considered less than '20' (but of course 100 is greater than 20).

Like others here, if possible use TRY_CAST() or TRY_CONVERT() for the conversion as it will avoid errors that could stop your queries from running.

CREATE TABLE Tests
    ([id] int, [int_as_string] varchar(20))
;
    
INSERT INTO Tests
    ([id], [int_as_string])
VALUES
    (1, '20000'),
    (2, '30000'),
    (3, '200'),
    (4, '1'),
    (5, '100'),
    (6, '20'),
    (7, '200')
;

Open in new window

select *
from (
      select
        t1.int_as_string t1_string
      , case when t1.int_as_string > t2.int_as_string then 'GT'
             when t1.int_as_string < t2.int_as_string then 'LT'
             when t1.int_as_string = t2.int_as_string then 'EQ'
        end strcomp
      , case when try_cast(t1.int_as_string as int) > try_cast(t2.int_as_string as int) then 'GT'
             when try_cast(t1.int_as_string as int) < try_cast(t2.int_as_string as int) then 'LT'
             when try_cast(t1.int_as_string as int) = try_cast(t2.int_as_string as int) then 'EQ'
        end intcomp
      , t2.int_as_string t2_string
      from tests t1
      cross join tests t2
       ) d
where strcomp <> intcomp
order by 
        t1_string
      , t2_string


| t1_string | strcomp | intcomp | t2_string |
|-----------|---------|---------|-----------|
|       100 |      LT |      GT |        20 |
|        20 |      GT |      LT |       100 |

Open in new window


select
  t1.int_as_string t1_string
, case when t1.int_as_string > t2.int_as_string then 'GT'
       when t1.int_as_string < t2.int_as_string then 'LT'
       when t1.int_as_string = t2.int_as_string then 'EQ'
  end strcomp
, case when try_cast(t1.int_as_string as int) > try_cast(t2.int_as_string as int) then 'GT'
       when try_cast(t1.int_as_string as int) < try_cast(t2.int_as_string as int) then 'LT'
       when try_cast(t1.int_as_string as int) = try_cast(t2.int_as_string as int) then 'EQ'
  end intcomp
, t2.int_as_string t2_string
from tests t1
cross join tests t2
order by 
  t1.int_as_string
, t2.int_as_string


**[Results][3]**:
| t1_string | strcomp | intcomp | t2_string |
|-----------|---------|---------|-----------|
|         1 |      EQ |      EQ |         1 |
|         1 |      LT |      LT |       100 |
|         1 |      LT |      LT |        20 |
|         1 |      LT |      LT |       200 |
|         1 |      LT |      LT |       200 |
|         1 |      LT |      LT |     20000 |
|         1 |      LT |      LT |     30000 |
|       100 |      GT |      GT |         1 |
|       100 |      EQ |      EQ |       100 |
|       100 |      LT |      GT |        20 |
|       100 |      LT |      LT |       200 |
|       100 |      LT |      LT |       200 |
|       100 |      LT |      LT |     20000 |
|       100 |      LT |      LT |     30000 |
|        20 |      GT |      GT |         1 |
|        20 |      GT |      LT |       100 |
|        20 |      EQ |      EQ |        20 |
|        20 |      LT |      LT |       200 |
|        20 |      LT |      LT |       200 |
|        20 |      LT |      LT |     20000 |
|        20 |      LT |      LT |     30000 |
|       200 |      GT |      GT |         1 |
|       200 |      GT |      GT |         1 |
|       200 |      GT |      GT |       100 |
|       200 |      GT |      GT |       100 |
|       200 |      GT |      GT |        20 |
|       200 |      GT |      GT |        20 |
|       200 |      EQ |      EQ |       200 |
|       200 |      EQ |      EQ |       200 |
|       200 |      EQ |      EQ |       200 |
|       200 |      EQ |      EQ |       200 |
|       200 |      LT |      LT |     20000 |
|       200 |      LT |      LT |     20000 |
|       200 |      LT |      LT |     30000 |
|       200 |      LT |      LT |     30000 |
|     20000 |      GT |      GT |         1 |
|     20000 |      GT |      GT |       100 |
|     20000 |      GT |      GT |        20 |
|     20000 |      GT |      GT |       200 |
|     20000 |      GT |      GT |       200 |
|     20000 |      EQ |      EQ |     20000 |
|     20000 |      LT |      LT |     30000 |
|     30000 |      GT |      GT |         1 |
|     30000 |      GT |      GT |       100 |
|     30000 |      GT |      GT |        20 |
|     30000 |      GT |      GT |       200 |
|     30000 |      GT |      GT |       200 |
|     30000 |      GT |      GT |     20000 |
|     30000 |      EQ |      EQ |     30000 |

http://sqlfiddle.com/#!18/ad80c/8
 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.