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

if column contains value then
excel if column contains value, then
if cell contains specific text then return value
excel if range contains value
excel if cell contains partial text
excel check if cell contains text from list
excel if range contains multiple values
if cell contains (multiple text criteria) then return (corresponding text criteria)

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

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

Excel formula: Value exists in a range, To determine if a range or column contains specific text (a specific substring or partial text), you can use a formula based on the COUNTIF function and wildcards . Use the Contains method to confirm the existence of a column before you perform additional operations on the column. The method returns false when two or more columns have the same name but different namespaces. The call does not succeed if there is any ambiguity when matching a column name to exactly one column. Applies to See also. CanRemove

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

Excel formula: If cell contains, In the event that we are required to test if a column or row contains specific information/text (partial text or a specific substring), we are going to utilize the COUNTIF� I have a table and in that one column contains few values as below. requirement : i want get the count of rows with some conditions like below: 1. Count of row which contains only "first" 2. Count of row which contains "first" and "Second" 3. Count of row which contains "first" but not "Second" or "third" etc. Sample column posted below:

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

How to determine if range contains specific text in Excel, To check if a cell contains specific text, you can use the ISNUMBER and the SEARCH function in Excel. There's no CONTAINS function in Excel. 1. To find the position of a substring in a text string, use the SEARCH function. CONTAINS (column_name, substring, label, policy_hint) Column_name and substring are the same as they are with SQL Server. Label must be a number and it represents the score for the CONTAINS function. It is optional except when you have to use CONTAINS more than once in a query. If you use the SCORE function, it must refer to this value.

Contains Specific Text, Let's say you want to ensure that a column contains text, not numbers. Or, perhapsyou want to find all orders that correspond to a specific salesperson. If you� Currently using COUNTIF(I$8:O$30,B36) which is on the most part doing the job. Cell B36 will contain 2 or the initials, eg GP or TV, which works fine but what I need to do is count if the cells contains GP when cell b36 contains GP/TV. The formula COUNTIF(I$8:O$30,B36) does not then pick up and count that cell because it is not just GP.

Check if a cell contains text (case-insensitive), If we need to find a text string in Excel, if cell contains the same text. Use a combination of the following Certain text. Use the formula under the TEXT column� Check if a column contains text using SQL. Ask Question Asked 7 years, 1 month ago. Active 6 months ago. Viewed 189k times 19. I have a column

How to lookup cells having certain text and returns the Certain Text , If you are looking for an Excel formula to find cells containing specific text and sum the corresponding values in another column, use the SUMIF� When you select New column, the Formula bar appears along the top of the Report canvas, ready for you to name your column and enter a DAX formula. By default, a new calculated column is named Column. If you don’t rename it, additional new columns will be named Column 2, Column 3, and so on.

Comments
  • Please edit the question to specify (maybe with a tag) which RDBMS you are querying.
  • gsub("-$", "", txt)
  • thanks @M-M it needs to be in sqldf unfortunately
  • 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?
  • Question: does sqldf supports REGEXP?
  • I haven’t tried to use regex in sqldf, but the question has been asked before stackoverflow.com/questions/33026213/…
  • Thanks for the answer. Maybe it's time for a major update of sqldf
  • 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