Breaking up fields in a table

[Dave5430]

I have a tble that contains a Phone_number field That I want to break it into two fields AC and Number
Phone_Number ----> AC Number
4041234567 404 1234567

Can anyone please tell me how to do this, I would really apreciate it.

Thanks,
Dave5430

 

[hneal98]

in your query, on the Field row of your query, put this:

AC:left([Phone_Number],3)

that is for the AC field, for the Number field, do this:

Number: right([Phone_Number],7)

And that should do it so long as you don't have any numbers like this:

(555)555-5555
555-555-5555
etc.

 

[Dave5430]

Thanks hneal98 for such a promt answer, however i am not sure I understand your answer. forgive my ignorance I'm new at SQL server 2k
Is it an update statement?
will that work for all rows in my table in one statement?
Can I do it through DTS where I can save the package for future use?

I would not mind copying table1 with the the Phone_number to table2 with an AC and number with the data in table1 going to table2 (but split in two)

Hope that's Clear to everyone.

Thanks,
Dave5430

 

[qwertyguy]

How about:

BEGIN TRAN
UPDATE dbo.tbl SET
AC=LEFT(RTRIM(LTRIM([Phone_Number])),3),
Number=RIGHT(RTRIM(LTRIM([Phone_Number])), LEN(RTRIM(LTRIM([Phone_Number])))-3)
WHERE LEN(RTRIM(LTRIM([Phone_Number])))>3
COMMIT TRAN

 

[hneal98]

Sorry, I was in another mode when i wrote that. disregard it. You need to leave out the left and right and use substring. You would use qwertyguy's solution if you alread created a table called tbl.

Do like this:

UPDATE dbo.tbl
SET AC = MID(RTRIM(LTRIM([Phone_Number])),1,3),
Number = MID(RTRIM(LTRIM([Phone_Number])), 4, 7)

Sorry about goof above.