0

I am moving data from a column in one database to a column in another database using the INSERT INTO command using Squirrel SQL v3.7.

The field I am moving is a character field for telephone numbers that allowed open entries. The receiving field however should disregard all letters and symbols and only enter in the format ##########

Is there a simple way to do this? The other solutions I've seen have been very involved.

Nakilon
  • 34,866
  • 14
  • 107
  • 142
TripWire
  • 71
  • 1
  • 10
  • Are you extracting from DB2 and inserting into DB2? Could you handle the replace at the source database? – Walucas Nov 25 '15 at 19:31
  • The first thing that jumps to my mind is regular expressions. http://stackoverflow.com/questions/4763757/regular-expressions-in-db2-sql – Dan Bracuk Nov 25 '15 at 19:34
  • Possible duplicate of http://stackoverflow.com/questions/17462802/how-to-find-special-characters-in-db2/17469776#17469776 – mustaccio Nov 25 '15 at 20:59

2 Answers2

0

Try this when extracting, or only the translate when inserting the data:

select translate('+ 4854 BBBB cCc 12','ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!@#$%^&*()-=+/\{}[];:.,<>? ',' ') from yourTable;
Walucas
  • 2,549
  • 1
  • 21
  • 44
  • I think you mixed up the "from" and "to" strings. – mustaccio Nov 25 '15 at 20:58
  • And seems likely to be incomplete for the domain of *all letters and symbols* except for those, perhaps aptly described as, USA-centric. A specification of hex code points devoid of only the digits would be quite complete; perhaps to excess, and would be dependent on encoding scheme. – CRPence Sep 25 '16 at 01:25
0

I'm not specifically familiar with Squirrel sql, but the easiest way to match non-numeric is with regex. Specifically [^0-9] will match anything in the string that is not a number. I was able to obtain the result described above on my system (ibm iseries) with a:

select regexp_replace(column1,'([^0-9])','') from table1
Ryan
  • 76
  • 7