Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
robert693
Starting Member
42 Posts |
 Posted - 2009-07-15 : 12:26:48
|
| Hello,I have a table called External. The fields in it are First Name, Last Name, Address, Phone, Email. The email field is not currently used and we are going to use it for a fax program. I am putting into the email field a string with the first name, last name, and the fax number. The fax number is not currently formatted to what we need so I want to change it. It needs to have no punctuation of any kind. Also some of the numbers have a 1 before the area code and others do not. I need the number to change from, for example, (309) 555-2582 to 13095552582. Somebody here helped me in the past and showed me the replace function. I have this query so far: UPDATE [External] SET EMail = ('RFAX: '+First_Name+', '+Last_Name+', '+Suffix+'@/FN='+Replace(Replace(Replace(Replace(Fax,'(',''),')',''),'-',''),'/',''));Should I put in an If statement?Thank you for all your help! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-07-15 : 12:33:52
|
| didnt understand what your problem is. didnt above query work? as i see, it looks fine except for fact that you missed the prefix 1 |
 |
|
|
robert693
Starting Member
42 Posts |
Posted - 2009-07-15 : 12:47:31
|
| It worked fine Thank you for the help! I need to put a 1 in where there is none and account for the numbers that already have a 1. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-07-15 : 13:02:53
|
| for that just do like .... other values +CASE WHEN LEFT(Fax,1) <> '1' THEN '1' ELSE '' END + Replace(Replace(Replace(Replace(Fax,'(',''),')',''),'-',''),'/','')); |
|
|
|
robert693
Starting Member
42 Posts |
Posted - 2009-07-20 : 13:39:31
|
| Would the syntax be different if I were to test this in MS Access? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-07-24 : 13:53:31
|
| yup...for access related queries plzz post in access forum |
|
|
|
|
|
|
|
|
If statemant in a query?