string to integer in postgres

Question

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.

Use a regex match?

Jiří Baum
– Jiří Baum

2021-07-07 09:35:50 +00:00
Commented Jul 7, 2021 at 9:35 — Jiří Baum
– Jiří Baum, Commented Jul 7, 2021 at 9:35

Salman Arshad · Accepted Answer · 2021-07-07 09:35:02Z

3

You can use regexp to find such rows:

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

answered Jul 7, 2021 at 9:35

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Frank Heikens · Accepted Answer · 2021-07-07 09:35:33Z

1

Something like this?

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

answered Jul 7, 2021 at 9:35

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2021-07-07 10:23:02Z

1

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.

answered Jul 7, 2021 at 10:23

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Collectives™ on Stack Overflow

string to integer in postgres

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related