How to TRIM OR TRUNCATE first character in DB2 column

Question

I have column phone number with value and want to update the table column such that country code doesnt exist. 19083452345 14084456789 12023458900 18163456789

I tried to use LPAD, LTRIM but couldnt succeed.

can't you use SUBSTR to get everything after the first character? — data_henrik, May 21 '19 at 12:19

score 0 · Accepted Answer · answered May 21 '19 at 12:22

0

Something like this using SUBSTR:

update tablename set phone=substr(phone,2,length(phone)-1)

answered May 21 '19 at 12:22

data_henrik

16,724
2
28
49

Thank you it worked. But I had one more requirement, if Phone has "918-435-0000" and want to remove"-" result should be - 9184350000, I can use substring with position 4 and 8? or shall I be using replace function ? – Radha Jogendra May 21 '19 at 14:26

score 0 · Answer 2 · answered May 21 '19 at 19:27

Use regular expressions (regex). Before doing it, check for the way country code are being written in your database, what standard is being used, if any (E.164 is tendency, but not always followed). For example, a mobile phone number in UK may be registered as '07911 123456' or also '+44 7911 123456' (and with or without blanks). There is a great deal of regular expressions in pages such as http://regexlib.com/Search.aspx?k=phone+number&c=0&m=0&ps=20&p=11&AspxAutoDetectCookieSupport=1

I don't know what flavor of Db2 are you using (Db2 for IBM i, Db2 for zOS, or Db2 for LUW) and it may have small variations on the regex support among them. The following article explain how to use regex in Db2 11.1 for LUW: https://www.idug.org/p/bl/et/blogaid=605

How to TRIM OR TRUNCATE first character in DB2 column

2 Answers2