今天在做PG数据到HIVE的数据交换任务时，因为某个字段在PG中是Varchar类型，hive是bigint，而偏偏PG 中该字段的存储值都被加了小数点位，导致字段类型转换失败。
现在就需要将字符串中小数点后的部分给截掉。
开始时尝试使用的是CHARINDEX来获取小数点的位置,然后使用substring函数截取该位置之前的数值。
select CAST(SUBSTRINg(sal_qty, 1 , CHARINDEX('.',sal_qty)-1)as bigint)
但是运行时发现PG中没有CHARINDEX函数。
SQL 错误 [42883]: ERROR: function charindex(unknown, character varying) does not exist 建议：No function matches the given name and argument types. You might need to add explicit type casts.
在官方文档中查找相应的替换函数
https://www.postgresql.org/docs/current/functions-string.html
找到一个position函数
position ( substring text IN string text ) → integer
Returns first starting index of the specified substring within string, or zero if it's not present.
position('om' in 'Thomas') → 3
测试一下
select sal_qty, cast(SUBSTRINg(sal_qty, 1 , position ('.' in sal_qty) - 1) as bigint) as sal_qty_int from table_test
返回结果，符合预期。
另外还可以尝试使用strpos函数，功能和position相同，但要注意参数位置
strpos ( string text, substring text ) → integer
Returns first starting index of the specified substring within string, or zero if it's not present. (Same as position(substring in string), but note the reversed argument order.)
strpos('high', 'ig') → 2
select sal_qty, cast(SUBSTRINg(sal_qty, 1 , strpos(sal_qty,'.')-1)as bigint) as sal_qty_int from table_test
