matching partial policy numbers in a query

  • Thread starter Thread starter Alex Martinez
  • Start date Start date
A

Alex Martinez

Thanks to Jerry Whittle help I was able to get rid of the prefix in the
policy number so I can the match the policy number of the claims table to
the policy number in the Actual table, but now I have encounter a new
problem I hope someone can help me. Below is the code to get rid of the
prefix in the policy Number. The policy number goes like this
AP012345678965 All I want to do is to capture only "012345678" not the
prefix "AP" or the numbers following "965" to do a one to one policy match.
I need to resolve this. Any tips will be appreciated. Thank you in
advance.




SELECT Claims.Policy_Number AS ClaimsPolicyNumber,
Claims.PolicyDate,
Actual.Policy_Number AS ActualPolicyNumber,
Actual.ClaimsAmount
FROM Actual, Claims
WHERE Mid([Claims].[Policy_Number],3)=Mid([Actual].[Policy_Number],3);
 
Hi Alex,

If you want to extract 8 characters starting at the third character, use
expressions like this:

Mid([Actual].[Policy_Number], 3, 8)

If you want to drop the first two characters and the last three, use
expressions like this:

Mid([Actual].[Policy_Number], 3, Len([Actual].[Policy_Number]) - 5)



Thanks to Jerry Whittle help I was able to get rid of the prefix in the
policy number so I can the match the policy number of the claims table to
the policy number in the Actual table, but now I have encounter a new
problem I hope someone can help me. Below is the code to get rid of the
prefix in the policy Number. The policy number goes like this
AP012345678965 All I want to do is to capture only "012345678" not the
prefix "AP" or the numbers following "965" to do a one to one policy match.
I need to resolve this. Any tips will be appreciated. Thank you in
advance.




SELECT Claims.Policy_Number AS ClaimsPolicyNumber,
Claims.PolicyDate,
Actual.Policy_Number AS ActualPolicyNumber,
Actual.ClaimsAmount
FROM Actual, Claims
WHERE Mid([Claims].[Policy_Number],3)=Mid([Actual].[Policy_Number],3);
Click to expand...
 
Back
Top