|
|
|
|
|
|
|
|
|
|
Re: LEFT & Right functions to carve out data from a field [message #641288 is a reply to message #641276] |
Thu, 13 August 2015 00:28 |
|
Littlefoot
Messages: 21806 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Until recently, we (here, in Croatia) had a "number" which uniquely identified every person. It was called "JMBG" (something like the Social Security Number in the USA, unless I'm wrong). It consisted of 13 digits which represented certain information. I can't remember exactly what all of them were (don't feel like investigating now), but these were date of birth, gender, republic you were born in (that term goes back to former Yugoslavia which consisted of 6 republics), etc.
Therefore, if you needed someone's birthday, you just extracted it from the JMBG using SUBSTR. Or his/her gender. Or some other information.
I'm not saying that that information shouldn't be / wasn't stored in a separate column in databases spread throughout the country, but you certainly had a way to find that information using JMBG and I doubt that normalizing JMBG, i.e. having it split into several pieces of information and stored into separate columns and - therefore - losing JMBG itself as a whole - would be a good idea.
So, from my point of view, OP's data model might be flawed, or not. Can't tell due to lack of information.
|
|
|
|
Re: LEFT & Right functions to carve out data from a field [message #641295 is a reply to message #641290] |
Thu, 13 August 2015 02:20 |
|
Michel Cadot
Messages: 68625 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:Until recently, we (here, in Croatia) had a "number" which uniquely identified every person. It was called "JMBG" (something like the Social Security Number in the USA, unless I'm wrong).
We have the same thing in France, it is (for the general case, French born in the current metropolitan France territory):
GYYMMDDTTTNNNCC
| | | | | | |-> 2 digits, control code (97 minus the remainder of integer division of the first 13 digits by 97)
| | | | | |----> 3 digits, number of your birth in the town for the month you were born
| | | | | (hoping there are not more than 999 births in the month, big city are divided
| | | | | in "arrondissement" (district), 20 for Paris, each one with its number)
| | | | |-------> 3 digits, number of the town in the "département" (see below)
| | | |---------> 2 digits, "département" number (metropolitan France is divided in 96 administrative "départements")
| | |-----------> 2 digits, month of birth
| |-------------> 2 digits(!), year of birth
|---------------> 1 digit, gender 1: male, 2: female
But as there are many exceptions (as always in France, and exceptions on exceptions...), you can't rely on this number to determine the birth date (since there is not the day) or even the gender.
[Updated on: Thu, 13 August 2015 02:55] Report message to a moderator
|
|
|
|
Re: LEFT & Right functions to carve out data from a field [message #641330 is a reply to message #641288] |
Thu, 13 August 2015 07:23 |
|
EdStevens
Messages: 1376 Registered: September 2013
|
Senior Member |
|
|
Littlefoot wrote on Thu, 13 August 2015 00:28Until recently, we (here, in Croatia) had a "number" which uniquely identified every person. It was called "JMBG" (something like the Social Security Number in the USA, unless I'm wrong). It consisted of 13 digits which represented certain information. I can't remember exactly what all of them were (don't feel like investigating now), but these were date of birth, gender, republic you were born in (that term goes back to former Yugoslavia which consisted of 6 republics), etc.
...
So, from my point of view, OP's data model might be flawed, or not. Can't tell due to lack of information.
Which is why I said "Not 100% but very likely."
In auto manufacturing you have the Vehicle Identity Number(VIN). It is most definately a data element in itself, but but various parts do have various meanings. As I recall (it's been 10 years since I worked for an auto manufacturer) the VIN is 15 characters, and the last 6 is the numeric serial number of the car. Various parts of the first 9 characters indicate the company, the assembly plant, the model or vehicle type, etc.
So yes there are 'exceptions', but it is certainly a 'red flag' to be re-thought and pointed out to newbies. And often, not-so-newbies.
|
|
|
|