Selecting all rows with a column that doesn't contain any digits

JLCarveth 0
Viewed 28

I have been trying to select all records from a certain table within my PostgreSQL database using a simple regex. The table in question is a table of inventory items, and each item has a partno. There are a couple of entries in this table, whose part numbers are only letters (ie, DISCOUNT, REBATE, etc). Other part numbers are a combination of letters and numbers (ie, 100H, 250H300, etc.)

I want to delete these entries. Before deletion, I am trying to simply select the records, but I am failing.

I have tried some queries such as:

SELECT * FROM items WHERE partno ~ '\b([a-z]+)\b'; // Returns nothing

SELECT * FROM items WHERE partno ~ '[A-Z]+'; // This returns *everything*

SELECT * FROM items WHERE partno ~ '\b[^\d\W]+\b'; // Returns nothing

Especially the last query I can't figure out why it's returning nothing. Is there a better way to return all records where partno has no digits?

I don't know how to edit this to make it clear that I did my homework and none of the established answers are working for me. I guess the examples of what I've tried and how it doesn't work somehow isn't enough? PostgreSQL 14

postgresql
2 Answers

You said 2 different things:

1/ if you are looking for part numbers which are only letters (ie, DISCOUNT, REBATE, etc) and part numbers which are a combination of letters and numbers (ie, 100H, 250H300, etc.)

select * 
  from items
 where partno !~ '^\d$';

2/ if you are looking for all records where partno has no digits as @Belayer said , this will do it

 select * 
  from items
 where partno !~ '\d';
Lionel-fr0

You are looking for partno has no digits. The regexp operator !~ says find columns that do not match the expresion, so just look for at last 1 digit. (see demo)

select * 
  from items
 where partno !~ '\d';
Belayer0
Related