Strip Character

[prasadgov]

Hi,

I have a field where the values are as
36B ESTT--FAMT-50000000,
36B ESTT--FAMT-20000,
36B ESTT--FAMT-1500000,
I need only the numeric values after FAMT/ and without commas.
I need to use in my query.

TIA

 

[theDBguy]

Just a thought but maybe use the InStrRev() function to find the last "-" character?

 

[The_Doc_Man]

If it is always "FAMT-" then for hypothetical field X, and assuming you want the number AS A NUMBER (vs. as a string), ...

CLNG( REPLACE( RIGHT( X, LEN( X ) - INSTR( 1, X, "FAMT-" ) + 5 ) , ",", "" ) )

If you didn't want a number but just a string, strip off the CLNG() and just use the rest. This is not the only way to do it, but if (for example) you have one of those strings where the trailing comma is omitted, the REPLACE won't do anything so should be save. If there is the chance that something other than FAMT- might be your marker, this WILL NOT WORK correctly.

Now if you REALLY wanted to do this safely and use it in a query and wanted control over options, the best method might be to write a public string function to take the whole field and return only that part that you wanted, doing the extraction a part at the time. Note that VBA is less efficient than the functions, but only slightly so, because doing the above in a query does involve the VBA programming environment as well.

 

[Gasman]

If it is always "FAMT-" then for hypothetical field X, and assuming you want the number AS A NUMBER (vs. as a string), ...

CLNG( REPLACE( RIGHT( X, LEN( X ) - INSTR( 1, X, "FAMT-" ) + 5 ) , ",", "" ) )

If you didn't want a number but just a string, strip off the CLNG() and just use the rest. This is not the only way to do it, but if (for example) you have one of those strings where the trailing comma is omitted, the REPLACE won't do anything so should be save. If there is the chance that something other than FAMT- might be your marker, this WILL NOT WORK correctly.

Now if you REALLY wanted to do this safely and use it in a query and wanted control over options, the best method might be to write a public string function to take the whole field and return only that part that you wanted, doing the extraction a part at the time. Note that VBA is less efficient than the functions, but only slightly so, because doing the above in a query does involve the VBA programming environment as well.

Wouldn't Val() and InstrRev() be simpler?

 

[prasadgov]

This worked perfectly

 

[prasadgov]

another question ..If I numbers as below

ESTT_Settled
7498469,02
6248724,18
6248724,18
34429680,
13389320,
17910566,58

where I replace , with a decimal so that it is as below. I need to add "00" after the decimal, to such values

ESTT_Settled
7498469.02
6248724.18
6248724.18
34429680.
13389320.
17910566.58

How to account for values like 34429680, and 13389320,

 

[The_Doc_Man]

another question ..If I numbers as below

ESTT_Settled
7498469,02
6248724,18
6248724,18
34429680,
13389320,
17910566,58

where I replace , with a decimal so that it is as below. I need to add "00" after the decimal, to such values

ESTT_Settled
7498469.02
6248724.18
6248724.18
34429680.
13389320.
17910566.58

How to account for values like 34429680, and 13389320,

Yes and no. If the numbers are being read using VAL() or something like that, they will be stored correctly. Which makes me ask if these numbers are for display only? But generally, if those are INPUT numbers, you can later display them with a FORMAT function that includes two decimal places. There is no difference (internally) between 34429680. and 34429680.00 IF that same field correctly displays 6248724.18 later.

Now, if those are STRINGS, that is a strange inconsistency of use, since normally numbers with decimal fractions are stored either as DOUBLE, CURRENCY, or scaled LONG. What did you intend to do with the numbers.

 

[prasadgov]

Yes and no. If the numbers are being read using VAL() or something like that, they will be stored correctly. Which makes me ask if these numbers are for display only? But generally, if those are INPUT numbers, you can later display them with a FORMAT function that includes two decimal places. There is no difference (internally) between 34429680. and 34429680.00 IF that same field correctly displays 6248724.18 later.

Now, if those are STRINGS, that is a strange inconsistency of use, since normally numbers with decimal fractions are stored either as DOUBLE, CURRENCY, or scaled LONG. What did you intend to do with the numbers.

I agree that there is no difference (internally) between 34429680. and 34429680.00. Since it is for an higher-up, it's mostly for display purpose!

 

[Gasman]

another question ..If I numbers as below

ESTT_Settled
7498469,02
6248724,18
6248724,18
34429680,
13389320,
17910566,58

where I replace , with a decimal so that it is as below. I need to add "00" after the decimal, to such values

ESTT_Settled
7498469.02
6248724.18
6248724.18
34429680.
13389320.
17910566.58

How to account for values like 34429680, and 13389320,

I seem to recall you already asked this in another thread?
Why not just use a certain format?

 

[ebs17]

I have a field where the values are as
36B ESTT--FAMT-50000000,
36B ESTT--FAMT-20000,
36B ESTT--FAMT-1500000,

The real problem is - once again - the use of compound data.

After applying the rules of normalization, you would have at least four separate fields, and the number requested would actually be in a field with a number data type and immediately and freely usable, and index use would also be possible in subsequent use.

Compounding is easier than separating - if expressions like the ones above are really needed. But this is just for the sake of illustration.

 

[DickyP]

Simply use a regular expression.

Public Function StripHeader(pstrIn As String) As String
     Static RE As New VBScript_RegExp_55.RegExp
 
    RE.IgnoreCase = True
    RE.Global = True
    RE.Pattern = "(.{1,10}FAMT-|,)"

 
    If RE.test(pstrIn) Then
        StripHeader = RE.Replace(pstrIn, Space(1))
    Else
        StripHeader = pstrIn
    End If
 
End Function