Support Questions

Find answers, ask questions, and share your expertise

Convert String to Int

New Contributor

How to convert String Into Int in the SQL table

1 REPLY 1

Cloudera Employee

 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)