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.
solving your SQL problem.
markjugg on 2006-10-02T19:25:16
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 formats in both places.
An intermediate solution might be to create a view that appears to do that, while leaving the difference intact, for now.
Mark
Re:solving your SQL problem.
sigzero on 2006-10-03T00:32:34
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?
x on 2006-10-03T03:59:01
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:Or this?
x on 2006-10-03T04:06:39
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
jmcada on 2006-10-03T22:04:44
If the leading '1' is constant you could always go with changing the other column to match: select 1 || replace('MATA 0000001891', ' ') from dual
