Last 4 digits (bounced)

} } ----- Transcript of session follows ----- } Not in this domain - email root@cssnet.sanford.nc.us w/questions } } ----- Unsent message follows ----- } >From duke!mcnc!hpbs3645.boi.hp.com!jparker Wed Feb 8 18:45:42 1995 remote from wolves } > } > My brain must be on holiday, but I can't figure out how to compare the } > last 4 digits of a telephone number to a like field in my database. } > } > something like this: } > } > select * from table1 } > where table1.company = record.company } > and table1.city = record.city } > and table1.phone_no matches "*", record.phone_no[last 4 digits?] } > } > Of course the above doesn't work, and to top it off record.phone_no is a } > character field and may vary in length if it is a local, long distance or } > international number. Hmmm... What I'd give for a BASIC RIGHT$ } > function! } } If they're going to move around then you can't do it in SQL per se. You HAVE } to create another column (or a joining table ala fake index style) with the } last four digits in it. To populate that you can do: } } DECLARE curs CURSOR FOR SELECT key, phone_no from table } } FOREACH curs INTO key, phone_no } LET idx1 = LENGTH(phone_no)-4 } LET idx2 = idx1 + 4 } LET phone_no = phone_no[idx1,idx2] } INSERT INTO fake_idx_table VALUES (key, phone_no) } END FOREACH } } j. _____________________________________________________________________________ Jack Parker - Hewlett Packard, BSMC Boise, Idaho, USA jparker@hpbs3645.boi.hp.com _____________________________________________________________________________ Subtlety is the art of saying what you think and getting out of the way before it is understood. _____________________________________________________________________________ Any opinions expressed herein are my own and not those of my employers. _____________________________________________________________________________