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

Question

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

Please edit the question to specify (maybe with a tag) which RDBMS you are querying. — bignose, Mar 07 '19 at 22:05

BENY · Accepted Answer · 2019-03-07T22:26:36.677

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

edited Mar 07 '19 at 22:26

answered Mar 07 '19 at 22:21

BENY

317,841
20
164
234

That will remove *all* consecutive ‘-’ characters (zero, one, three, seventeen, any amount) from the right end of the value. That's different from what the question asks; I don't know whether that matters. @nak5120 does that change the answer? – bignose Mar 08 '19 at 00:18

Kerry Jackson · Answer 2 · 2019-03-07T22:52:58.820

1

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")

edited Mar 07 '19 at 22:52

answered Mar 07 '19 at 22:44

Kerry Jackson

1,821
12
20

Question: does `sqldf` supports `REGEXP`? – M-- Mar 07 '19 at 23:03
I haven’t tried to use regex in sqldf, but the question has been asked before https://stackoverflow.com/questions/33026213/regarding-sqldf-package-regexp-function?rq=1 – Kerry Jackson Mar 07 '19 at 23:58
Thanks for the answer. Maybe it's time for a major update of `sqldf` – M-- Mar 08 '19 at 00:02

score 0 · Answer 3 · answered Mar 07 '19 at 22:12

0

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 '%-')

answered Mar 07 '19 at 22:12

bignose

30,281
14
77
110

I get the same error unfortunately - this was my new code based on your answer - `df2<- sqldf("SELECT left([col_a], -1) AS col_a_truncated FROM df WHERE ([col_a] LIKE '%-")` Error is `Error in result_create(conn@ptr, statement) : near "(": syntax error` – nak5120 Mar 07 '19 at 22:19

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

3 Answers3