spark sql substr函数

开发中，经常进行模糊查询或者进行截取字符串进行模糊匹配，常用的就是substr函数或者substring函数。

使用语法: substr(string A, int start),substring(string A, int start) 两者用法一样,两个参数

           返回值: string

           说明：返回字符串A从start位置到结尾的字符串

举例演示：

spark.sql("select substring('abcdeffff',3) from data1").show()

spark.sql("select substring('abcdeffff',-1) from data1").show()


+-----------------------------------+
|substring(abcdeffff, 3, 2147483647)|
+-----------------------------------+
|                            cdeffff|
|                            cdeffff|
+-----------------------------------+


+------------------------------------+
|substring(abcdeffff, -1, 2147483647)|
+------------------------------------+
|                                   f|
|                                   f|
+------------------------------------+

hive> select substr(‘abcde’,1,2) 和selectsubstr(‘abcde’,0,2)结果一样ab，默认都是从第一位开始取.

    import spark.implicits._
    val data1 = Seq(
      ("4", "qi", "bj", "2019/09/02 12:12:12"),
      ("5", "SUN", "tj", "2019/09/02 13:13:13")
    ).toDF("useid", "name", "live", "ptime")
    
data1.show()
    data1.createOrReplaceTempView("data1")
+-----+----+----+-------------------+
|useid|name|live|              ptime|
+-----+----+----+-------------------+
|    4|  qi|  bj|2019/09/02 12:12:12|
|    5| SUN|  tj|2019/09/02 13:13:13|
+-----+----+----+-------------------+

spark.sql("select *, substr(ptime, 2,5) from data1").show()

spark.sql("select *, substr(ptime, 3,5) from data1").show()
+-----+----+----+-------------------+----------------------+
|useid|name|live|              ptime|substring(ptime, 2, 5)|
+-----+----+----+-------------------+----------------------+
|    4|  qi|  bj|2019/09/02 12:12:12|                 019/0|
|    5| SUN|  tj|2019/09/02 13:13:13|                 019/0|
+-----+----+----+-------------------+----------------------+

+-----+----+----+-------------------+----------------------+
|useid|name|live|              ptime|substring(ptime, 3, 5)|
+-----+----+----+-------------------+----------------------+
|    4|  qi|  bj|2019/09/02 12:12:12|                 19/09|
|    5| SUN|  tj|2019/09/02 13:13:13|                 19/09|
+-----+----+----+-------------------+----------------------+

语法: substr(string A, int start, int len),substring(string A, intstart, int len)，用法一样，三个参数

     返回值: string

    说明：返回字符串A从start位置开始，长度为len的字符串

举例演示：

spark.sql("select substring('abcdeffff',3,5) from data1").show()
+--------------------------+
|substring(abcdeffff, 3, 5)|
+--------------------------+
|                     cdeff|
|                     cdeff|
+--------------------------+

截取后5位;

截取起始超过自身长度：

截取长度超过自身长度:

spark.sql("select substring('abcdeffff',-5) from data1").show()
spark.sql("select substring('abcdeffff',10) from data1").show()
spark.sql("select substring('abcdeffff',12,5) from data1").show()
spark.sql("select substring('abcdeffff',3,25) from data1").show()

+------------------------------------+
|substring(abcdeffff, -5, 2147483647)|
+------------------------------------+
|                               effff|
|                               effff|
+------------------------------------+

+------------------------------------+
|substring(abcdeffff, 10, 2147483647)|
+------------------------------------+
|                                    |
|                                    |
+------------------------------------+

+---------------------------+
|substring(abcdeffff, 12, 5)|
+---------------------------+
|                           |
|                           |
+---------------------------+

+---------------------------+
|substring(abcdeffff, 3, 25)|
+---------------------------+
|                    cdeffff|
|                    cdeffff|
+---------------------------+