CODEWITHSUNDEEP
sqlpostgresql
a_dog_with_no_master
a_dog_with_no_master

Reputation: 65

string to integer in postgres

i have a table of users:-

|usersname|
|  52125  |
|  John   |
|  kathy  |
|  52451  |

I need to delete entries where username is made of only numbers like 52125 and 52451. i don't want to delete john and kathy. datatype of column is varchar and I'm using PostgreSQL. can someone help.

Upvotes: 0

Views: 138

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

If you want to actually remove the users from the table, use delete:

delete from t
    where username ~ '^[0-9]+$';

You can use this logic in a select to check the rows before deleting them.

Upvotes: 1

Frank Heikens
Frank Heikens

Reputation: 127086

Something like this?

SELECT username 
FROM users 
WHERE username !~* '([a-z])';

Upvotes: 1

Salman Arshad
Salman Arshad

Reputation: 272106

You can use regexp to find such rows:

SELECT *
FROM t
WHERE usersname ~ '^[0-9]+$' -- user name made only of numbers

Upvotes: 3

Related Questions