Delete specific digit in a number using PostgreSQL-CodePudding

I have a column named membership_number varchar(255) in the memberships table.

Here is some sample data:

0000001234
0000002345
0000003456

membership_number must have 10 digits. If a user enters a number that has less than 10 digits, then the missing places are filled with leading zeroes accordingly.

At the moment, I have some data in this column as follows:

00001234.0
00002345.1
00003456.2

I would like to delete the decimal point which is the 2nd to the last digit and then add a leading zero to handle all of these undesired membership numbers. What would be the best way to do this?

I am aware of SUBSTRING() and its parameters but couldn't make it work so far.

CodePudding user response：

Please backup your data before trying this.

Would that work?

UPDATE tablename SET membership_number = concat('0',replace(membership_number,'.','')) WHERE membership_number LIKE '%._'

CodePudding user response：

Substring is not the function you want, you want a combination of REPLACE and LPAD functions:

 select lpad( replace (membership_number, '.', ''), 10, '0')  
   from menberships;

(table name assumed) And why if it must be 10 digits do you define it as length up to 255?