如何从字符串列中提取数字? [英] How to extract number from string column?
本文介绍了如何从字符串列中提取数字?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我的要求是从comment
列中的注释列中检索订单号,并且始终以R
开头.订单号应作为新列添加到表中.
My requirement is to retrieve the order number from the comment column which is in a column comment
and always starts with R
. The order number should be added as a new column to the table.
输入数据:
code,id,mode,location,status,comment
AS-SD,101,Airways,hyderabad,D,order got delayed R1657
FY-YT,102,Airways,Delhi,ND,R7856 package damaged
TY-OP,103,Airways,Pune,D,Order number R5463 not received
预期输出:
AS-SD,101,Airways,hyderabad,D,order got delayed R1657,R1657
FY-YT,102,Airways,Delhi,ND,R7856 package damaged,R7856
TY-OP,103,Airways,Pune,D,Order number R5463 not received,R5463
我已经在spark-sql中尝试过,下面是我正在使用的查询:
I have tried it in spark-sql, the query I am using is given below:
val r = sqlContext.sql("select substring(comment, PatIndex('%[0-9]%',comment, length(comment))) as number from A")
但是,出现以下错误:
org.apache.spark.sql.AnalysisException: undefined function PatIndex; line 0 pos 0
推荐答案
您可以使用具有以下定义的regexp_extract
:
You can use regexp_extract
which has the definition :
def regexp_extract(e: Column, exp: String, groupIdx: Int): Column
(R\\d{4})
表示R
,后跟4位数字.您可以使用有效的正则表达式轻松容纳其他情况
(R\\d{4})
means R
followed by 4 digits. You can easily accommodate any other case by using a valid regex
df.withColumn("orderId", regexp_extract($"comment", "(R\\d{4})" , 1 )).show
+-----+---+-------+---------+------+--------------------+-------+
| code| id| mode| location|status| comment|orderId|
+-----+---+-------+---------+------+--------------------+-------+
|AS-SD|101|Airways|hyderabad| D|order got delayed...| R1657|
|FY-YT|102|Airways| Delhi| ND|R7856 package dam...| R7856|
|TY-OP|103|Airways| Pune| D|Order number R546...| R5463|
+-----+---+-------+---------+------+--------------------+-------+
这篇关于如何从字符串列中提取数字?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文