extracting consecutive numbers from a character field and using CAST function

[John192]

Hi,


How to extract consecutive numbers (let's say up to of length 6) from a character field whose length is 9 and these consecutive 6 digits can occur in this field at any position out of it's length( for this character type field's length) ?

also after filtering out these 6 numeric digits from this character field(whose length is 9) i want to use cast function so that i could perform below join SQL statement:-

select * from F1 A Left join F2 B on A.fld1 = cast(B.FLD2 AS INT)

But here FLD2 is currently of data type Character(with 9 length) and FLD1 is of data type DECIMAL with 6 length.



Thanks