SUBSTRING usage

> Hello, > I'm running ISQL 7.30 and IDS 9.2. > What I'm trying to do is put the "-"'s back into a column that stores > telephone numbers. The following is what I have tried. > Thanks, > --ch > > UPDATE member > SET phone = SUBSTRING(phone [1, 3]) > # ^ > # 201: A syntax error has occurred. > # > + '-' > + SUBSTRING(phone [4, 7]) > WHERE phone IS NOT NULL; > > > > Tried this too: > > > UPDATE member > SET phone = phone [1, 3] > + '-' > + phone [4, 7] > WHERE social IS NOT NULL; > # ^ > # 1213: Character to numeric conversion error > #

Craig Haarmeyer wrote in message <3a270868$1_2@news3.calweb.com>... >> Hello, >> I'm running ISQL 7.30 and IDS 9.2. >> What I'm trying to do is put the "-"'s back into a column that stores >> telephone numbers. The following is what I have tried. >> Thanks, >> --ch >> >> UPDATE member >> SET phone = SUBSTRING(phone [1, 3]) Is SUBSTRING even an operator? If so, it must be a new fella for the 9.xx engines (which I haven't played with yet). Your second attempt is closer, but the + operator you are attempting to use is wrong. The concatenation operator is || so you should say: UPDATE member SET phone = phone [1, 3] || '-' || phone [4, 7] WHERE social IS NOT NULL; Beware: if || gets a null on one side or the other, then the null propagates out. In your case that would be a good thing...