Compare Columns Where One Is Similar to Part of Another

Compare Columns Where One is Similar to Part of Another

Reverse the where, to something like this:

Fullname not like '%' + FirstName + '%' 

select where one column part of another

You have the right idea, but MySQL doesn't support the + operator for string concatination, only for mathematical addition. Instead, just use the concat function:

SELECT * FROM table_name WHERE column_b NOT LIKE CONCAT(column_a, '%')

How to use LIKE clause to compare two columns in the same table?

Your comparison is backwards. It should be:

WHERE column2 LIKE CONCAT('%', column1, '%');

Note that this will return a row like:

5 apple pineapple,grapefruit

If that's not appropriate, you shouldn't use LIKE. FIND_IN_SET is designed to match items in a column-delimited list, so you could use:

WHERE FIND_IN_SET(column1, column2)

However, make sure you don't have any spaces around the commas if you do this.

It would be much better if you normalized your table. Comma-delimited lists should not be used, you should use a many-to-many relation table.

How do I compare two columns for equality in SQL Server?

What's wrong with CASE for this? In order to see the result, you'll need at least a byte, and that's what you get with a single character.

CASE WHEN COLUMN1 = COLUMN2 THEN '1' ELSE '0' END AS MyDesiredResult

should work fine, and for all intents and purposes accomplishes the same thing as using a bit field.

compare multiple columns to one column and return names of columns that match condition

We loop across the columns that have '_Date' as suffix in column names, get the column name (cur_column()) if the values are greater than Death column, return as new column by modifying the .names, then use unite to join those _new column to a single one

library(dplyr)
library(tidyr)
df %>% 
   mutate(across(ends_with("_Date"),
    ~ case_when(.x > Death ~ cur_column()), .names = "{.col}_new")) %>% 
   unite(new, ends_with("_new"), na.rm = TRUE, sep = ", ") %>%
   na_if("")

-output

     Death First_Date Second_Date row_number                     new
1 2017-09-20 2016-09-09  2019-05-02          1             Second_Date
2 2017-09-20 2018-09-20  2019-09-20          2 First_Date, Second_Date
3 2017-09-20 2016-09-09  2016-09-09          3                    <NA>

---

NOTE: coalesce returns only the first non-NA value across rows

How to compare two columns and input the smaller one in a new column in pandas?

You could use min on axis:

df['col3'] = df[['col1','col2']].min(axis=1)

Output:

         col1        col2        col3
0  2015-01-03  2015-01-04  2015-01-03
1  2022-02-22  2017-01-02  2017-01-02

---

Related Topics