If Column contains "-" at the end of a value, remove the "-" at the end - sqldf
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
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
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
rtrim seems easier, here's a solution using
sqldf uses SQLite, which does not have the
LEFT function, so use the
SUBSTR function instead, and the
LEN function is
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.
- 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
- 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
- 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