use Perl

• solving your SQL problem. (Score:1)

  by markjugg (792)

  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
  • Re: (Score:1)

    by sigzero (5768)

    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.
• Or this? (Score:1)

  by x (5851)

  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, ' ', '') ?
  • Re: (Score:1)

    by x (5851)

    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, ' ', '')
    
• Replace and Concat (Score:2)

  by jmcada (5846)

  If the leading '1' is constant you could always go with changing the other column to match: select 1 || replace('MATA 0000001891', ' ') from dual