Postgresql: Remove hyphens and whitespaces

Question

I am currently working on DB data which contains whitespaces and hyphens. I searched over the net and found this Remove/replace special characters in column values? . I tried to follow the answer but I am still getting hyphens. I tried playing around with it, I can only remove the whitespace

conn_p = p.connect("dbname='p_test' user='postgres' password='postgres' host='localhost'")
conn_t = p.connect("dbname='t_mig1' user='postgres' password='postgres' host='localhost'")

cur_p = conn_p.cursor()
cur_t = conn_t.cursor()

cur_t.execute("SELECT CAST(REGEXP_REPLACE(studentnumber, ' ', '') as integer), firstname, middlename, lastname FROM sprofile")
rows = cur_t.fetchall()

for row in rows:
    print "Inserting ", row[0], row[1], row[2], row[3]
    cur_p.execute(""" INSERT INTO "a_recipient" (id, first_name, middle_name, last_name) VALUES ('%s', '%s', '%s', '%s') """ % (row[0], row[1], row[2], row[3]))

cur_p.commit()
cur_pl.close()
cur_t.close()

What I would like to achieve is if I got a studentnumber of 001-2012-1456, it will be displayed as 000120121456.

Answer 1

To wipe out all characters in a set efficiently use translate. It takes a set of characters to translate into another set of characters. If the other set is empty it deletes them.

test=> select translate('001-2012-145 6', '- ', '');
  translate  
-------------
 00120121456

---

While translate is simpler and faster for this particular job, it's important to know how to use regexes for others. To do it with regexp_replace there's two changes you need to make.

First, you have to match the set of - and as [- ].

Then, you have to specify to replace all occurrences, otherwise it will stop after the first one. That's done with the g flag.

test=> select regexp_replace('001-2012-145 6', '[- ]', '', 'g');
 regexp_replace 
----------------
 00120121456

Here's a tutorial on POSIX regular expressions and character sets.

Answer 2

Its very simple to use inbuilt translate function.

Example:

select translate('001-2012-145 6', '- ', '');

Output of above command : 00120121456