How to Select Rows Where a Column Value Starts with a Certain String

How do I select rows where a column value starts with a certain string?

You can do

select * from mytable where name like "Mr.%"

See http://www.sqlite.org/lang_expr.html

Select rows where column value starts with a string AND ends with an even number

You could use substr to extract the last three characters, to_number to treat them as a number, and then mod it by 2 to see if it's even:

SELECT *
FROM   mytable 
WHERE  mycolumn LIKE 'ABD%' AND MOD(TO_NUMBER(SUBSTR(mycolumn, -3)), 2) = 0

Select rows if string begins with certain characters in pandas

Use Series.str.startswith with convert list to tuple and filtering by DataFrame.loc with boolean indexing:

wdata = pd.DataFrame({'words':['what','and','how','good','yes']})

L = ['a','g']
s = wdata.loc[wdata['words'].str.startswith(tuple(L)), 'words']
print (s)
1     and
3    good
Name: words, dtype: object

Python.pandas: how to select rows where objects start with letters 'PL'

If you use a string method on the Series that should return you a true/false result. You can then use that as a filter combined with .loc to create your data subset.

new_df = df.loc[df[‘Code’].str.startswith('pl')].copy()

Select a row where column value start with string and does not start with string

There is no need of joins for this. Aggregation with a specific HAVING clause should do it:

SELECT name
FROM t
GROUP BY name
HAVING COUNT(CASE WHEN stringvalue LIKE 'WINDOWS%' THEN 1 END) > 0
AND    COUNT(CASE WHEN stringvalue LIKE 'MAC%'     THEN 1 END) = 0;

DB<>Fiddle

select rows where same column values start with string, 2 questions

Use SUBSTR(col1, LENGTH('$string')+1) to get the part of the column after the prefix, and group by this.

Use LIKE 'prefix%' to match a column beginning with a prefix.

SELECT SUBSTR(col1, LENGTH('$string')+1) AS suffix, COUNT(*) as count
FROM table
WHERE col1 LIKE '$string%'
GROUP BY suffix

Then you can use a loop to create an associative array with all the counts:

$counts = array();
while ($row = mysqli_fetch_assoc($query) {
    $counts[$row['suffix']] = $row['count'];
}
var_dump($counts);

Select rows where value of column A starts with value of column B

For row wise comparison, we can use DataFrame.apply:

m = df.apply(lambda x: x['A'].startswith(x['B']), axis=1)
df[m]

       A    B
0  apple  app
2     aa   aa

The reason your code is not working is because Series.str.startswith accepts a character sequence (a string scalar), and you are using a pandas Series. Quoting the docs:

pat : str

Character sequence. Regular expressions are not accepted.

Display row where string column starts with specific letter

Use single quotes! Double quotes are meant for identifiers only (such as column names, table names, and so on), hence the error you are getting.

On the other hand, single quotes stand for literal strings, which is what you mean here:

SELECT DISTINCT CITY
FROM STATION
WHERE CITY LIKE 'A%';

In that regard, Oracle strictly follows the ANSI SQL specification. There are other databases that allow double quotes for strings, and use other characters to quote identifiers (MySQL for example)... but I personally find that it makes things more confusing that they would need to.

Pandas dataframe - Select rows where one column's values contains a string and another column's values starts with specific strings

I suggest create each boolean mask separately for better readable code and then chain them by &:

prefixes = ['E','L','N']

m1 = ~dfTest_Data['trading_book'].str.startswith(tuple(prefixes))
m2 = dfTest_Data['state'].str.contains('Traded')

cols = ['originating_system_id','rbc_security_type1','state','trading_book']
df_Traded_Away_User = dfTest_Data.loc[m1 & m2, cols]
print (df_Traded_Away_User)
  originating_system_id rbc_security_type1        state trading_book
3                  RBCL               CORP  Traded Away      PDFEFGR

Pandas select rows where a value in a columns does not starts with a string

You can using startswith , the ~ in the front will convert from in to not in

prefixes = ['IM','JE','GE','GV','CHE','MCO']

df[~df.Name.str.startswith(tuple(prefixes))]
Out[424]: 
        Name  Remains
1  RT6000996     True
2  RT6000994     True
3  RT6000467     True
4  RT6000431     True

---

Related Topics