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

use Perl Log In

Log In

[ Create a new account ]

sigzero (5768)

sigzero
  (email not shown publicly)

I am the proverbial "accidental" programmer. I have found that I really like Perl and programming so I am pursuing that route now. I am the Debian systems administrator and junior Perl programmer for a company called Inspire.

Journal of sigzero (5768)

Monday October 02, 2006
01:09 PM

Any SQL gurus out there?? (using Oracle)

[ #31197 ]

I have to use a substr I think.

In one table I have a record like:

1MATA0000001891

and in another table I have the records like:

MATA 0000001891

I need to be able to match the second one to the first one and SQL is not my forte yet.

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