Format Field
lazarus / 16-Databases

----------------------------------------------------------------------
InfoMan · 2012-06-24 18:11

I'm using Lazarus with Firebird 1.1 from 14/06/2012 2.5.1.26351 and
Windows 7 Ultimate 32 Bit.

I would like to help me standardize one of the fields in my database.
It is this: In the database, there are about 8500 records. It turns
out that in about 7500 records Telephone field (which stores the phone
number is in data format (035) 1234-5678, ie, it shows the DDD (area
code) 3 digits and the other 1000 records the field is the same data
format (35) 1234-5678, ie, it shows the area code with two digits.

How do I standardize the data records of 7500 so that they show the
data as the other, ie the two-digit area code, ie, (35) 1234-5678. Is
there any SQL command or Delphi autoamticamente to do it for me? I do
not want to do this manually would take too long since.

Many thanks to all who respond.

----------------------------------------------------------------------
taazz · 2012-06-26 06:10

update <Table_Name> set <Table_Name>.<Field_name> = '(' ||
SUBSTRING(<Table_name>.<Field_Name> FROM 3)
where SUBSTRING(<Table_Name>.<Field_Name> FROM 1 FOR 2) = '(0';

Replace <Table_name> with your table name and <Field_Name> with your
phone's field name and run it make sure that all the fields you want
to change do start with this 2 characters '(0'

you can see which fields are going to be changed if you take the where
part of the script and put under a select * from <Table_Name> just to
make sure that you will not alter any other phones by mistake.

Regards