If Column contains “-” at the end of a value, remove the “-” at the end - sqldf

问题: I have a dataframe like below: ColA ColB djdn- 3 dn-dn 5 ndmc- 8 nd-nd-md 9 Expected Output: ColA ColB New_Col...

问题:

I have a dataframe like below:

    ColA    ColB
   djdn-       3
   dn-dn       5
   ndmc-       8
nd-nd-md       9

Expected Output:

    ColA    ColB   New_Col
   djdn-       3      djdn
   dn-dn       5     dn-dn
   ndmc-       8      ndmc
nd-nd-md       9  nd-nd-md

Using sqldf, I want to remove the "-" at the end of the value if it exists at the end.

This is my attempted code:

library(sqldf)
df_new<- sqldf("select CASE(RIGHT([ColA], 1) = '-', LEFT([ColA], LEN([ColA])-1), 
[ColA]) [New_Col] from df")

Error in result_create(conn@ptr, statement) : near "(": syntax error

回答1:

I think you looking for rtrim

library(sqldf)
df_new<- sqldf("select ColB,rtrim(ColA,'-') as ColA from df")
  ColB     ColA
1    3     djdn
2    5    dn-dn
3    8     ndmc
4    9 nd-nd-md

回答2:

While using rtrim seems easier, here's a solution using substr: sqldf uses SQLite, which does not have the RIGHT or LEFT function, so use the SUBSTR function instead, and the LEN function is LENGTH.

library(sqldf)
df_new <- sqldf("select df.*, 
               CASE 
                WHEN substr(ColA, length(ColA),1) = '-' THEN substr(ColA, 1, length(ColA)-1) 
               ELSE ColA
               END AS New_Col from df")

回答3:

To match “value contains ‘-’ at the end”, use (I'll assume PostgreSQL) a pattern match:

SELECT
    col_a
FROM df
WHERE (col_a LIKE '%-')

Then, to get the value without its final character (which you now know is a ‘-’ character), use a string manipulation function:

SELECT
    left(col_a, -1) AS col_a_truncated
FROM df
WHERE (col_a LIKE '%-')
  • 发表于 2019-03-12 19:31
  • 阅读 ( 202 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除