Re: how can we retrieve just the numeric part from a character field

On Feb 23, 10:17 am, "Maya" <mayamanju...@xxxxxxxxx> wrote:
On Feb 23, 10:01 am, "Nishant Rupani" <nishant.rup...@xxxxxxxxx>
wrote:





On Feb 23, 8:55 am, "Maya" <mayamanju...@xxxxxxxxx> wrote:

On Feb 22, 9:00 pm, saragir...@xxxxxxxxxxx wrote:

On Feb 22, 9:08 am, "Maya" <mayamanju...@xxxxxxxxx> wrote:

I have 2 tables and i have to map a decimal field in table A with a
character field in table B such that only the numeric part of the
character field is taken.

Hey Maya ... could you give an example of the data you are talking
about? ... it would be helpful in developing scenarios to help you.

Cheers,

Sara ...

I'm doing a testing for some fields between table A and B.
The fields i'm trying to map are the document numbers for each record
and are like the unique key.
So to check for the other fields i need to map these 2 tables using
this field.
In table A, the doc# is decimal of length 7 and in table B its
character variable of length 8.
Also B.doc# has alphabets in them.
But i need to map only the numeric part of B.doc# to A.doc#.

For instance if B.doc# is A12345, then the corresponding A.doc# will
be 12345.

Also if there are alphabets in B.doc#, Only after the alphabets the
numerals will follow.
There will not be any B.doc# as 12C456

Maya,

Your explanation itself answers your question. Try something like
this:
create table nishant_test (col1 varchar(10) null)
create table nishant_test2 (col1 int null)

insert into nishant_test values ("A12345")
insert into nishant_test2 values (12345)

select t1.* from nishant_test t1, nishant_test2 t2
where convert(int, substring(t1.col1 , 2, (char_length(t1.col1) -
1)) ) = t2.col1- Hide quoted text -

- Show quoted text -

I guess i didnt explain it enough.
There can be n number of alphabets in the character field. Not just
one
For instance
1. B.doc = abc456 and A.doc = 456
2. B.doc = ab 567 and A.doc = 567- Hide quoted text -

- Show quoted text -

You have to have a loop then.

1. Pick up each char one by one starting from first char and check
where you find fist number. You can do it by ascii or (between "0" and
"9"). Then take rest of the string and convert to number
OR
2. Try to find out first occurance of 0 to 9 in the string then
take minimum of that and take substring of the string starting from
that point to the end of the string.
OR
3. if char length is constant. Say it is 5. ex "ab123". First
check if "ab123" is between "00000" and "99999". If not then check if
"b123" between "00000" and "99999". If not then check if "123" between
"00000" and "99999". So you get the number

Please add if I have missed anything.

.