Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Convert String to Int

avatar
New Contributor

How to convert String Into Int in the SQL table

1 REPLY 1

avatar
Expert Contributor

 With the help of cast udf function, you can convert a string to an integer.

cast(str_column as int)

Reference - https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-TypeConversion...

If the value is between –2147483648 and 2147483647, cast(string_filed as int) will work.

0: jdbc:hive2:Hive> select cast('2147483647' as int);
INFO  : Compiling command(queryId=hive_20220121054634_f3485a1b-692a-412d-8fe4-1c616c21d0aa): select cast('2147483647' as int)
INFO  : Semantic Analysis Completed (retrial = false)
INFO  : Created Hive schema: Schema(fieldSchemas:[FieldSchema(name:_c0, type:int, comment:null)], properties:null)
INFO  : Completed compiling command(queryId=hive_20220121054634_f3485a1b-692a-412d-8fe4-1c616c21d0aa); Time taken: 0.17 seconds
INFO  : Executing command(queryId=hive_20220121054634_f3485a1b-692a-412d-8fe4-1c616c21d0aa): select cast('2147483647' as int)
INFO  : Completed executing command(queryId=hive_20220121054634_f3485a1b-692a-412d-8fe4-1c616c21d0aa); Time taken: 0.009 seconds
INFO  : OK
+-------------+
|     _c0     |
+-------------+
| 2147483647  |
+-------------+
1 row selected (0.296 seconds)
0: jdbc:hive2://hive> select cast('2147483648' as int);
INFO  : Compiling command(queryId=hive_20220121054651_15cbd283-7de5-4310-bbe9-b262ebbd1e07): select cast('2147483648' as int)
INFO  : Semantic Analysis Completed (retrial = false)
INFO  : Created Hive schema: Schema(fieldSchemas:[FieldSchema(name:_c0, type:int, comment:null)], properties:null)
INFO  : Completed compiling command(queryId=hive_20220121054651_15cbd283-7de5-4310-bbe9-b262ebbd1e07); Time taken: 0.059 seconds
INFO  : Executing command(queryId=hive_20220121054651_15cbd283-7de5-4310-bbe9-b262ebbd1e07): select cast('2147483648' as int)
INFO  : Completed executing command(queryId=hive_20220121054651_15cbd283-7de5-4310-bbe9-b262ebbd1e07); Time taken: 0.006 seconds
INFO  : OK
+-------+
|  _c0  |
+-------+
| NULL  |
+-------+
1 row selected (0.086 seconds)