How do I extract an integer from a string value in SQL?

Question

I need to extract an integer in this string. How would I do this using MySQL or PostgreSQL?

user_id='12345'

In this string I must get the number 12345.

And by "extract from" you actually mean to convert the whole string, not some part of it? — Erwin Brandstetter
– Erwin Brandstetter, Commented Jul 11, 2012 at 17:11

Daniel Li · Accepted Answer · 2012-07-24 23:40:59Z

10

You would use the CAST function.

SELECT CAST(user_id AS INT) FROM Table

Reference: http://dev.mysql.com/doc/refman/5.5/en/cast-functions.html#function_cast

answered Jul 11, 2012 at 16:28

Daniel Li

15.6k6 gold badges45 silver badges60 bronze badges

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

Comments

Community · Accepted Answer · 2017-05-23 11:48:12Z

4

Besides the standard SQL syntax demonstrated by @Daniel Li, there is also the PostgreSQL specific shorthand:

user_id::int

The manual will happily tell you about this really basic stuff ...

CommunityBot

11 silver badge

answered Jul 11, 2012 at 17:05

668k160 gold badges1.2k silver badges1.3k bronze badges