sql again..?

Question

i have a table.it contains column named tel_hp.so, i want to correct the tel_hp records; which the tel_hp has less number from the standard or more number from the standard or the tel_hp which has the dash character (-).
then from that query and records amendment, i want to put it into a new table.
does anybody know what is the sql command for it?
thanks...

Answer 1

Assuming you are on Oracle DB (why not, about 80-90% are)...

This should try and help you:

SELECT * FROM YOURTABLE WHERE LENGTH(TEL_HP) <> SOMENUMBER;

The above should give you a list of rows where TEL_HP is NOT EQUAL to the character length you are looking for.

From there you can nest that into an INSERT statement after you create a similar table.

Example,

INSERT INTO NEWTABLE (SELECT * FROM YOURTABLE WHERE LENGTH(TEL_HP) <> SOMENUMBER)

Answer 2

create table amendment select * from table_original where tel_hp not like '104724729' or tel_hp like '%-%'

grant all on amendment to public

in the first query what you are doing is selecting all the records which dont match a specific number and putting them into the table amendment.Also the records with tel_hp containing a - are put into the new table.

The second query is to grant access permissions for update,insert,delete,select..everything

Answer 3

declare @tel_hp as Varchar(255)

declare CursorName cursor scroll for
select tel_hp from Table1
where tel_hp < Standard
Or tel_hp > Standard
Or tel_hp like '%-%'

open CursorName
fetch next from CursorName into @tel_hp
while @@fetch_status = 0
begin
insert onto NewTable(tel_hp)
values(@tel_hp)
fetch next from CursorName into @tel_hp
end
close CursorName
deallocate CursorName

Answer 4

The basic syntax is:

CREATE TABLE new_table
AS (SELECT * FROM old_table);


For example:

CREATE TABLE suppliers
AS (SELECT *
FROM companies
WHERE id > 1000);

Hope this helps, I am not 100% sure what you are looking for. But I think this is it...


Enjoy -