How to convert varchar to int in SQL

Question

How do you convert varchar to int in SQL? My code is as such but I've tried casting and converting but they don't work. Declare/set doesn't work either.

select 
    Branch.BranchNo, 
    COUNT(Member.MemberID) as 'Number of Members' 
from 
    Branch
where 
    'Number of Members' > 2

Error:

Conversion failed when converting the varchar value 'Number of Members' to data type int.

You don't really want to convert varchar to int -- the problem you're having is that it's trying to compare the words 'Number of Members' to the integer 2, so it's trying to convert. What you really want is where [Number of Members] > 2, I think, or just where COUNT(Member.MemberID) > 2. — Jacob Mattison
– Jacob Mattison, Commented Jun 3, 2014 at 20:08
SELECT Branch.BranchNo, COUNT(Member.MemberID) as 'Number of Members' FROM Branch ........ GROUP BY Branch.BranchNo HAVING COUNT(Member.MemberID) > 2 — Linger
– Linger, Commented Jun 3, 2014 at 20:09
'Number of Members' is saved as a varchar ,specifically the number 9 right now, but I can't compare it to an int number since it's saved as a varchar. Thank you for the suggestion but I got error" Invalid column name" instead — user3290048
– user3290048, Commented Jun 3, 2014 at 20:12

Vulcronos · Accepted Answer · 2014-06-03 20:11:02Z

4

Use:

select Branch.BranchNo, COUNT(Member.MemberID) as 'Number of Members' from Branch
......
HAVING COUNT(Member.MemberID) > 2

Sql runs in the following order: from where group by having select order by.

You can't use the column alias in any clause except the order by.

You need to use the count function again instead of the alias you gave. Also, you can't use an aggregate function in the where clause. Where runs before any aggregation happens in the group by clause. Even if you don't explicitly have a group by clause sql will consider the entire result set as a group.

If you want to filter on an aggregate like sum/count/max it must happen in the having clause.

answered Jun 3, 2014 at 20:11

Vulcronos

3,4563 gold badges19 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lennart - Slava Ukraini · Accepted Answer · 2014-06-03 20:10:04Z

1

You need group by and having

select Branch.BranchNo, COUNT(Member.MemberID) as "Number of Members" 
from Branch
group by Branch.BranchNo
having COUNT(Member.MemberID) > 2

answered Jun 3, 2014 at 20:10

Lennart - Slava Ukraini

7,2111 gold badge23 silver badges37 bronze badges

Comments

marc_s · Accepted Answer · 2014-06-03 20:19:50Z

0

SELECT
    BRAND.BRANCHNO,
    COUNT{MEMEBER.MEMBERID) AS [NUMBER OF MEMBERS]
FROM 
    BRANCH
GROUP BY 
    BRAND.BRANCHNO
HAVING 
    COUNT(MEMBER.MEMBERID) > 2

edited Jun 3, 2014 at 20:19

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jun 3, 2014 at 20:10

madmannrw

112 bronze badges

Comments

Jonathan Simas · Accepted Answer · 2014-06-03 20:27:29Z

0

You cannot use grouping functions as count in "where" statements. Try to use HAVING instead:

select Branch.BranchNo, COUNT(Member.MemberID) as 'Number of Members' from Branch HAVING COUNT(Member.MemberID) > 2

Anyway, to convert VARCHAR to INT will depends of your DB vendor:

Oracle: TO_NUMBER(varcharValue)

SQL Server: CAST(varcharValue AS INT)

Postgres: CAST(varcharValue AS INTEGER)

answered Jun 3, 2014 at 20:27

Jonathan Simas

3203 silver badges10 bronze badges

Collectives™ on Stack Overflow

How to convert varchar to int in SQL

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related