Hi Scott,
what client are you using to execute the query?
As it does not do what you normally would expect,
I can only guess what is wrong.
If I run the query as described in query analyzer it gives the result
you want to have:
(sample code provided)
create table Main (cId int identity(1,1) primary key, cName
varchar(100), cPhoneNr varchar(10))
insert into Main (cName, cPhoneNr) values('Test A','123456781')
insert into Main (cName, cPhoneNr) values('Test B','123456782')
insert into Main (cName, cPhoneNr) values('Test C','123456783')
insert into Main (cName, cPhoneNr) values('Test D','123456784')
select * from Main
update Main set Main.cPhoneNr = '0' + Main.cPhoneNr
select * from Main
Tobias
Scott Berry wrote:
Tobias,
Thanks. I tried the query with '+' sign
UPDATE Main
SET Main.PhoneNr = '0' + Main.PhoneNr
but it set all my field values to '0' without the existing 9 characters.
Any other thoughts?
Scott
"Tobias Marquart" <to******@gmx.de> wrote in message
news:41********@maser.urz.unibas.ch...
Scott Berry wrote:
This is a little frustrating, because it should be easy.
I have an application that has been converted to SQL Server 2000 from MS
Access 97. New data is loaded each week and one of my old queries will no
longer work to assist with formatting the data.
I have a Phone Number field that is Varchar 10
The numbers have imported without the leading zero.
In access I used to run
UPDATE Main
SET Main.PhoneNr = '0' & Main.PhoneNr
This does not work in SQL Server.
Any suggestions?
string concatenation is done with the plus-sign in SQL-Server.
so
UPDATE Main
SET Main.PhoneNr = '0' + Main.PhoneNr
should work.
Other possible problems:
where PhoneNr not like '0*'
becomes
where PhoneNr not like '0%'
NULL-values might also be handled different then you are used to.
hth,
Tobias
