Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • For a short term or one-time solution, I would read up on the string parsing and regular expresssions functions in Oracle.

    As a general recipe you could parse one version of this string and reconstruct to match against the other. For example, using substr() (in SQL) for string extraction and using "||" to join the result back together. Depending on the actual problem, you may need to use Regex power.

    For a longer term solution, it would be ideal to normalize that data in database, and store it in ideal format
    • Unfortunately I cannot change the database. They did wondeful things with it. Only using CHAR even if it was a DATE and it is ALL padded with spaces. If the data is 8 characters long in a 10 charater field then I get 2 padded spaces. I have rtrim() all over the place. It sucks.

      I "may" be able to do the view...not sure if I am allowed to do even that. So that leaves me with the substr() option I guess.

  • create table a (str varchar2(100)); create table b (str varchar2(100)); insert into a (str) values ('1MATA0000001891'); insert into b (str) values ('MATA 0000001891'); commit; select * from a, b where a.str like '%' || replace(b.str, ' ', '') ?
    • create table a (str varchar2(100));
      create table b (str varchar2(100));

      insert into a (str) values ('1MATA0000001891');
      insert into b (str) values ('MATA 0000001891');

      commit;

      select * from a, b
      where a.str like '%' || replace(b.str, ' ', '')
  • If the leading '1' is constant you could always go with changing the other column to match: select 1 || replace('MATA 0000001891', ' ') from dual