Extract SUB String using SELECT Statement

[riaarora]

Hi All,

How to extract a portion on based of search criteria from a Column string using SELECT statement.

I have a table with below structure:

Table A Structure
=============
Name
Description
Transaction
Value

Name: James
Description: Redemption: MMTIME AUD, 3.295%, 25.07.12-01.08.12 (90562)
Transaction:Close Loan: Mortgage SGD, 3.2625%, 24.07.12-07.08.12, 264308
Value:$1092

I want to extract 90562 from Description
and 264308 from Transaction



but issue is length of the field is not fixed. First field will be between "()" e.g. (90562)
and second field will be after , e.g. ",264308"

Appreciate your help.

[orange]

You would do yourself an major favor if you were to design your tables with explicit fields. Too often people, using DBMS with little background, don't do the required design and use the "shoebox method". A few text or memo fields and plop in whatever takes your fancy. Then the realization that you can't do much with a "shoe box" full of disjoint things that now have special meaning, and may even be related.

You have lost/hidden the meaning of each of these substrings and now must go through contortions to find, extract and use them.

You may find your first string with the (), but your second string has a space after the comma - so it won't work as you have shown.

Is the second string you need always 6 contiguous digits? Is it always the last part of the field?