Extract SUB String using SELECT Statement

[ria.arora]

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.

 

[plog]

First, how come your data is like this? This data should be normalized, specifically each discrete piece of data should be in its own field--you shouldn't have multiple pieces of data in one field.

Second, you are going to need to use the MID function (http://www.techonthenet.com/access/functions/string/mid.php) and the InStr function (http://www.techonthenet.com/access/functions/string/instr.php) to accomplish this.

 

[jdraw]

cross posted at
http://www.accessforums.net/queries/extract-sub-string-using-select-statement-27870.html