| Author |
Message
|
| EvolutionQuest |
 Posted: Wed Feb 25, 2004 6:37 am Post subject: Parsing inconsistent data |
 |
|
Voyager
Joined: 18 Sep 2001
Posts: 88
Location: Billings, MT
|
I am trying to figure out the best method in handling a lastname with a suffix. For example the suffix could look like: ' CPA ', ',CPA', ' CPA.', or ',CPA.' minus the single quote. I am thinking the firstname, lastname, and suffix should be within a compound type fullname. The thing that throws me is the different patterns that the suffix could be coming in. Any suggestions would be greatly appreciated.
chris |
|
| Back to top |
|
 |
| Missam |
| Posted: Wed Feb 25, 2004 6:42 am Post subject: |
|
|
Chevalier
Joined: 16 Oct 2003
Posts: 424
|
| Can you be more familiar with your requirement ,what are you try to do ,are you constructing a MRM(TDS) if it is ,is it fixed length or tagged delimited. |
|
| Back to top |
|
|
| EvolutionQuest |
| Posted: Wed Feb 25, 2004 7:16 am Post subject: |
|
|
Voyager
Joined: 18 Sep 2001
Posts: 88
Location: Billings, MT
|
This would be an MRM that would look something like:
TAG|Prefix|FirstName|LastNameSuffix|Rest of fields delimited by pipe symbol~ |
|
| Back to top |
|
|
| Missam |
| Posted: Wed Feb 25, 2004 7:20 am Post subject: |
|
|
Chevalier
Joined: 16 Oct 2003
Posts: 424
|
| Is there a PIPE delimiter between lastname and suffix |
|
| Back to top |
|
|
| EvolutionQuest |
| Posted: Wed Feb 25, 2004 7:23 am Post subject: |
|
|
Voyager
Joined: 18 Sep 2001
Posts: 88
Location: Billings, MT
|
|
| Back to top |
|
|
| wmqiguy |
| Posted: Wed Feb 25, 2004 1:10 pm Post subject: |
|
|
Centurion
Joined: 09 Oct 2002
Posts: 145
Location: Florida
|
Chris:
It sounds like you have two variable-length fields being treated as one. Hunt down the designer of this format and explain why another pipe character would be handy! I hate to say this, but I'm not sure there is a way to do this....at least not elegantly. 
I'm shooting from the hip here, but is the data coming in camel-case? Assuming that the first charater of the suffix is capitalized (Phd, Jr, CPA, etc.), you could walk through the field and look for the second Capitalized character and then treat all characters from that point forward as the Suffix. (Like I said, this isn't elegant.) Otherwise, I'm not sure there is a way to do this.
Good Luck! |
|
| Back to top |
|
|
| Missam |
| Posted: Wed Feb 25, 2004 1:32 pm Post subject: |
|
|
Chevalier
Joined: 16 Oct 2003
Posts: 424
|
| Quote: |
Assuming that the first charater of the suffix is capitalized (Phd, Jr, CPA, etc.), you could walk through the field and look for the second Capitalized character
|
Just Curious to ask,IS there any function in ESQL that tell whether a character in a string is Caps or Lows |
|
| Back to top |
|
|
| wmqiguy |
| Posted: Wed Feb 25, 2004 1:46 pm Post subject: |
|
|
Centurion
Joined: 09 Oct 2002
Posts: 145
Location: Florida
|
| Nope. You would have to check each character against the alphabet. I never said this was pretty! |
|
| Back to top |
|
|
| wooda |
| Posted: Thu Feb 26, 2004 3:55 am Post subject: |
|
|
Master
Joined: 21 Nov 2003
Posts: 265
Location: UK
|
This should be able to be modelled in TDS.
You have a delimted structure.
within which you need to model the Lastname-suffix part as a substructure and use data element separation of Pattern.
This will allow you to use regular expresion to separate the suffix from the lastname.
I'm sure this should be possible but may require a bit of thought to figure out the best patterns to use.
This will depend on exactly which forms the suffix may take.
Do you always get a suffix?
Is it always of one of the following forms ?
' CPA '
',CPA'
' CPA.'
',CPA.' |
|
| Back to top |
|
|
| EvolutionQuest |
| Posted: Thu Feb 26, 2004 10:23 am Post subject: |
|
|
Voyager
Joined: 18 Sep 2001
Posts: 88
Location: Billings, MT
|
There are a number of suffixes, but the CPA suffix seems to be the odd ball of the group. Don't ask me why they did this, and one solution that I think will go forward is to move this logic into a Stored Procedure which is able to handle the &#^# better.
I still am going to pursue a solution via TDS, but at least my headache is over  |
|
| Back to top |
|
|
|
|
