Help with a strange mySQL query... :S

seavolvox

Hello,

I'm not sure if here is the right place to place this question... It's about a complex query that I cannot build... let's try 🙂

There's a table with colum A, B and C.

Colum A contains rows, like:

ROW1: "Abcdef ghijk 395.245.322 lmno pq rstu"
ROW2: "225-427.822 abc defghi lmpo"
ROW3: "ab 157 886.554 dkkkdj sosk"
ROW4: "554 25(52)3 3995 lldfkeke dlkd"

Colum B:

ROW1: "USER1"
ROW2: "USER2"
ROW3: "USER3"
ROW4: "USER4"

I need a query that returns a result, that includes the 4 rows, with the result:

"395245322" and "USER1"
"225427822" and "USER2"
"157886554" and "USER3"
"554255233995" and "USER4"

... that is, maybe with the REPLACE() function replace " ", "(", ")", "." and "-" with "" (nothing = delete), and then return any group of numbers.

In this case, returned numbers can be from any lengh. That's ok.

Would it be possible to return only numbers with 9 chars of lengh? Or this should be parsed with a PHP command?

Another question is: What would happen if ROW1 were...

ROW1: "Abcdef ghijk 395.245.322 lmno pq rstu 21.588(9)553"

... would return "2" numbers? (395245322 and 215889553)

Thank you in advance for your help!

Rgds.

dagon

garbage in garbage out, what ever column a is suppose to be inserting what you are actully going to use in the first place is the best solution.

seavolvox

dagon;10929753 wrote:
garbage in garbage out, what ever column a is suppose to be inserting what you are actully going to use in the first place is the best solution.

Yes, the problem is that that colum stores a text, and need to extract all group of numbers :S

johanafm

Then you should store that data separately. Decent normalization requires that each column contains only atomic data, and that you have no redundant data.

I'm not sure if "atomic data" is still actually part of 1NF, since there never was a clear definition of what atomic data is. Either way, I believe it is good to keep in mind. I have no idea what your data is supposed to be, so let's use the classical address example which is "Somestreet 123" or "123 Somestreet". As long as you just want to be able to print the address it can be considered atomic for all practical purposes.
But if you for some reason want to extract the street name and/or number, then it obviously can no longer be considered atomic. That's when you break it up and store Street and Street number separately.

And the reason you can't (well, shouldn't) just store the number you want to extract separately, as an addition to the string, is the problem you are in when updating. You would risk updating only the string or only the number, thus ending up with corrupt data.

seavolvox

johanafm;10929797 wrote:
Then you should store that data separately. Decent normalization requires that each column contains only atomic data, and that you have no redundant data.

...

And the reason you can't (well, shouldn't) just store the number you want to extract separately, as an addition to the string, is the problem you are in when updating. You would risk updating only the string or only the number, thus ending up with corrupt data.

I agree with you, but the information from the database is stored since 2003, and we need to update it to a better way, that's why we would extract phone numbers from the "text field" and store it in another field, as seems that phone numbers where stored without being formated and mixed with text. I know it's crazy, but that's what we've found :S

johanafm

Pure SQL won't help you. You may be able to solve it using vendor specific scripting languages such as pl/sql. So either that, or write code outside the db, in whatever language you prefer, to deal with your problem.
It would be simple to do in php.
Loop over the result set using while($row = $mysqli->fetch_assoc())
preg_match('#\d{3}.\d{3}.\d{3}#, $row['your_field_with_phone_numbers'], $matches);
if (count($matches)) loop through $matches[0] and do whatever you need to with the phone numbers

Roger_Ramjet

If you just want to get the phone numbers out and if they are all going to be just exactly 9 digits, which I very mush doubt, then you can do it just with sql. However, like any data cleanse, this is going to take more than 1 pass at the data.

Now which sql functions you can use depends upon which database it is. In MySQL you can use String Comparison, mostly LIKE, or you can also use Regular Expressions.

I have spent a lot of time doing this type of data extract and cleanse and can tell you that it is always going to take multiple passes over the data and then you will probably still have some left over that you are going to have to do manually. Up to you whether you decide to do it with queries or in code.

One last point, just because phone numbers look like numbers, they are not - you can not do arithmetic on them. They are TEXT and must be stored in a text field. Otherwise you will loose leading zeros and get other types of corruption. Always do a lot of validation on them BEFORE you allow them into your database and protect the database with table constraints, or you will spend your life cleaning them over and over again