help with SQL statement syntax (1 Viewer)

[chewy]

I have a sql statement. It is a phone number field. I want to if there is a 1 infront of the phone number to take it out. The phone would look like this:

1 (315) 344-3312

The statement I am using is this:

UPDATE Customers
SET Customers.Phone = (mid(Customers.Phone,2,len(Customers.Phone)-1))
WHERE ((left(Customers.Phone,1) = '1 '))


Im not sure how the mid works. ANy help would be appreciated!

 

[The_Doc_Man]

Well, you are working too hard.

The length argument of Mid$ (3rd argument) is OPTIONAL. If you omit it, Mid returns all characters from the start position to the end of the string. Honest! Look at Mid Function in the Help files.

Personally, I would probably use something like this:

UPDATE Customers
SET Customers.Phone = Mid( Customers.Phone, 2 )
WHERE
( Len(Customers.Phone) = 10 ) AND
( Left(Customers.Phone, 1) = "1" ) ;

This catches the case of the 1-xxx-xxx-xxxx phone numbers (with the dashes removed), which I assume is what you wanted to catch.

 

[chewy]

this is not Access it is SQL Server. Will that make a difference?

 

[fuzzygeek]

Code

You wrote

UPDATE Customers
SET Customers.Phone = (mid(Customers.Phone,2,len(Customers.Phone)-1))
WHERE ((left(Customers.Phone,1) = '1 '))

What will the other phone numbers look like
" (808) 555 8888"
"(808) 555 8888"

Your example shows the space between the 1 and the (.
Where you show the 2 should be 3 in the mid statement to remove the "1 ", ie the one and the space.

Make sure that the layout is consistent. For example, nothing like " 1 (808)..." with a space and a 1 in the first two positions.

 

[chewy]

Mid is not a recognized Access statement. Does anyone know what the SQL Server equivelant would be?

The phone number should look like this:

"(555) 555-5555"

 

[fuzzygeek]

SQL Server

Substring(string, starting position, length) is the equivalent of mid in SQL Server. Right function and Left function are the same.

 

[chewy]

Thank! I'll shall try tomorrow

 

[fuzzygeek]

MSDN Site

The Transact-SQL Reference at MSDN is a good place to find things like this. See the hyperlink:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_setu-sus_6btz.asp

 

[chewy]

Here is what I ended up using:

UPDATE Customers
SET Customers.Phone = (mid(Customers.Phone,2,14))
WHERE ((left(Customers.Phone,1) = '1'))

I forgot I actually was writing to a temporary Access Db before importing into SQL Server

Thanks for everyones help!