Deleting unwanted characters in a field
Deleting unwanted characters in a field
(OP)
Hello ISQL people,
This is probably an easy one but I'm finding it difficult since I am not too great at ISQL. I have a field that contains phone numbers and want to delete the ( ) -.
ex.. (212)555-1212 needs to be 2125551212
I can do this in T-SQL
as
update stage_guest
set phone = replace(phone,'(','')
go
update stage_guest
set phone = replace(phone,')','')
go
update stage_guest
set phone = replace(phone,'-','')
go
Anyone have an ISQL (Interbase 5.5) method?
I would appreciate any assistance, Thanks!
This is probably an easy one but I'm finding it difficult since I am not too great at ISQL. I have a field that contains phone numbers and want to delete the ( ) -.
ex.. (212)555-1212 needs to be 2125551212
I can do this in T-SQL
as
update stage_guest
set phone = replace(phone,'(','')
go
update stage_guest
set phone = replace(phone,')','')
go
update stage_guest
set phone = replace(phone,'-','')
go
Anyone have an ISQL (Interbase 5.5) method?
I would appreciate any assistance, Thanks!
Talk To Other Members
RE: Deleting unwanted characters in a field
Download and install the 'free UDF library' or 'rFunc UDF library'. You can find in both package enhanced string functions like replace.
RE: Deleting unwanted characters in a field
I tried the UDF's on some other test data and suprisngly they work pretty good on data manipulation. Great advice, Thanks! I didn't know that existed...
FYI for the PERL:
use DBI;
use dbd::odbc;
$dbh = DBI->connect("dbi:ODBC:sourcedb", "username", "password");
#phones
#select (id,phone) from guest
$sth = $dbh->prepare("select id,phone from guest");
$sth->execute();
while (@row = $sth->fetchrow_array)
{
if ($row[1] =~ /-/ or $row[1] =~ /\(/ or $row[1] =~ /\)/)
{
print "@row\n";
$del = $row[1];
$del =~ s/-//g;
$del =~ s/\(//g;
$del =~ s/\)//g;
print "$del\n";
$update = $dbh->prepare("update guest set phone = \"$del\" where id = $row[0]");
$update->execute;
}
}
#update guest
#set phone = $del where id = $row[0]