My cat's SQL smells like SQL

[Spooner]

alright....

i need to extract a group of people's phone numbers from an Oracle system into a simple text file.

It needs to be in a VERY specific format "(xxx) xxx-xxxx"

The problem I'm running into is that the field the person has to enter their phone number in Oracle is an open field and they're entered in all different ways. How can I write a function (or a series of functions) that puts them all into the same above format

 

[IcemanJer]

er... do some sort of regular expression..?
me don't know the specifics for Oracle, but sounds like that would be a way to go.

 

[Beau]

Again, are you only using SQL, or can it be pulled using a different scripting language?

 

[Spooner]

Originally posted by: Beau6183
Again, are you only using SQL, or can it be pulled using a different scripting language?

SQL and PL/SQL my friend........ same interface as before, different field

 

[notfred]

perl code to convert any phone number to your specific format (This should work on just about anything with 10 digits in it):

$phone_number =~ s/^[^\d]*(\d{3})[^\d]*(\d{3})[^\d]*(\d{4}).*$/($1) $2-$3/;

entire perl script to convert file w/ unformatted phone #'s to nicely formatted phone #'s (one number on each line):

#/usr/bin/perl

(open IN, "<input_filename.txt") || die "can't open input";
(open OUT, ">output_filename.txt") || die "can't open output";

while ( <IN> ){
chomp $_;
$_ =~ s/^[^\d]*(\d{3})[^\d]*(\d{3})[^\d]*(\d{4}).*$/($1) $2-$3/;
print OUT "$_\n";
}

close IN;
close OUT;

 

[Beau]

notfred... must be in SQL or PL/SQL

But just for the hell of it, here's a VBScript to do it:

Function FormatUSTelephone(byVal strPhoneNumber)
Dim strTemp
strTemp = Null
If Len(Trim(strPhoneNumber & "")) < 10 Then
strTemp = ""
Else
For i = 1 to Len(strPhoneNumber)
If IsNumeric(Mid(strPhoneNumber, i, 1)) Then
strTemp = strTemp & Mid(strPhoneNumber, i, 1)
End If
Next
If Left(strTemp, 1) = "1" Then
strTemp = Right(strTemp, Len(strTemp) - 1)
End If
If Trim(Len(strTemp)) <> 10 Then
strTemp = ""
Else
strTemp = "(" & Left(strTemp, 3) & ") " & Mid(strTemp, 4, 3) & "-" & Right(strTemp, 4)
End If
End If
FormatUSTelephone = strTemp
End Function

 

[Beau]

Originally posted by: Spooner

Originally posted by: Beau6183
Again, are you only using SQL, or can it be pulled using a different scripting language?

SQL and PL/SQL my friend........ same interface as before, different field

I'm still new to PL/SQL, so give me a few on this one... still learning string manipulation methods.

 

[NogginBoink]

Can you pull it into a text file and THEN format the data from the text file?

I'd cheat: pull it into a comma delimited file and use Excel to format it. For a one-off conversion this would work, but for a regularly scheduled data dump wouldn't be practical.

 

[Spooner]

Originally posted by: NogginBoink
Can you pull it into a text file and THEN format the data from the text file?

I'd cheat: pull it into a comma delimited file and use Excel to format it. For a one-off conversion this would work, but for a regularly scheduled data dump wouldn't be practical.

THought of that, but after the format is all complete... this needs to be all automated, so can't

Oh, and guys posting their Perl and VB.... that's great, now give me some SQL

 

[Beau]

What do you want it do do with the #'s that can't be formatted correctly (ie, ones that aren't 10 characters long, or that are alphabetic)?

 

[notfred]

you could do something like this, maybe:

system ( perl -e "$pn='123 456 7890'; $pn =~ s/^[^\d]*(\d{3})[^\d]*(\d{3})[^\d]*(\d{4}).*$/($1) $2-$3/; print $pn" )



I have no idea if you can system calls from SQL, but if you replace '123 456 7890' with your unformatted phone number, it will return the properly formatted one

 

[Spooner]

Originally posted by: Beau6183
What do you want it do do with the #'s that can't be formatted correctly (ie, ones that aren't 10 characters long, or that are alphabetic)?

they're pretty much all 10 numbers long, without alphabetic characters

but some people do this "xxxxxxxxxx" while other do this "xxx-xxx-xxxx" then this "xxx-xxxxxxx"

pain in the ass

:disgust:

 

[Beau]

Originally posted by: Spooner

Originally posted by: Beau6183
What do you want it do do with the #'s that can't be formatted correctly (ie, ones that aren't 10 characters long, or that are alphabetic)?

they're pretty much all 10 numbers long, without alphabetic characters

but some people do this "xxxxxxxxxx" while other do this "xxx-xxx-xxxx" then this "xxx-xxxxxxx"

pain in the ass

:disgust:

Okay, but just incase, do you just want it to output a null, or format whatever it can?
Trying to work a little error handling in

 

[Spooner]

Originally posted by: notfred
you could do something like this, maybe:

system ( perl -e "$pn='123 456 7890'; $pn =~ s/^[^\d]*(\d{3})[^\d]*(\d{3})[^\d]*(\d{4}).*$/($1) $2-$3/; print $pn" )



I have no idea if you can system calls from SQL, but if you replace '123 456 7890' with your unformatted phone number, it will return the properly formatted one

dude, what's the dilly with all the perl? we're all impressed, but that ain't helpin

 

[Spooner]

Originally posted by: Beau6183

Okay, but just incase, do you just want it to output a null, or format whatever it can?

Null it, but in the format "(xxx) xxx-xxxx"

 

[Spooner]

right now my select statement reads

SELECT pp1.parent_id, '('||substr(pp1.phone_number,1,3)||')'||' '||substr(pp1.phone_number,5,3)||'-'||
substr(pp1.phone_number,9,4)
FROM per_phones pp1,
per_people_x p
WHERE pp1.parent_table = 'PER_ALL_PEOPLE_F'
AND pp1.phone_type='H1'
and pp1.parent_id = p.person_id

 

[CPA]

So what does this have to do with your cat?

 

[Spooner]

Originally posted by: CPA
So what does this have to do with your cat?

I don't even have a cat... i was being amusing

 

[notfred]

Originally posted by: Spooner

Originally posted by: notfred
you could do something like this, maybe:

system ( perl -e "$pn='123 456 7890'; $pn =~ s/^[^\d]*(\d{3})[^\d]*(\d{3})[^\d]*(\d{4}).*$/($1) $2-$3/; print $pn" )



I have no idea if you can system calls from SQL, but if you replace '123 456 7890' with your unformatted phone number, it will return the properly formatted one

dude, what's the dilly with all the perl? we're all impressed, but that ain't helpin

I don't know SQL, this is the best I could do

 

[amnesiac]

can't you make it parse the hyphens and spaces to return a 10 digit number then format the result?

 

[Spooner]

Originally posted by: amnesiac 2.0
can't you make it parse the hyphens and spaces to return a 10 digit number then format the result?

how do i strip out the dashes and such beforehand?

 

[Beau]

Originally posted by: Spooner

Originally posted by: amnesiac 2.0
can't you make it parse the hyphens and spaces to return a 10 digit number then format the result?

how do i strip out the dashes and such beforehand?

Use the replace function:

REPLACE(pp1.phone_number, '-', '')

 

[MaxDSP]

u guys are making this too difficult. Just write:

int main
{

cout<<XXX-XXX-XXXX;
return 0;
}



and your done










<is smart and knows that was C++ and not sql

 

[Jzero]

I bent my schema.

 

[Mr.IncrediblyBored]

Originally posted by: Beau6183

Originally posted by: Spooner

Originally posted by: amnesiac 2.0
can't you make it parse the hyphens and spaces to return a 10 digit number then format the result?

how do i strip out the dashes and such beforehand?

Use the replace function:

REPLACE(pp1.phone_number, '-', '')

Yep, normalized data is a Good Thing?. A couple questions/comments:
1) Make sure you check the length of the string before hand so you don't get any erroneously typed phone numbers.
2) For f*cks sake buy a SQL book.
3) What happened with the issue you were having the other day?