Update Query question.

[Scott Berry]

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?

[Tobias Marquart]

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

[Scott Berry]

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.d e> wrote in message
news:41******** @maser.urz.unib as.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

[Tobias Marquart]

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.d e> wrote in message
news:41******** @maser.urz.unib as.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

[Rowland]

"Scott Berry" <[e-mail] > wrote in message
news:X3******** **********@news-server.bigpond. net.au...

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.d e> wrote in message
news:41******** @maser.urz.unib as.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

You said the numbers had imported without the leading zero - are you sure
that they weren't imported as a numeric value? If they had been you could
try,
SET Main.PhoneNr = '0' + CAST (Main.PhoneNr AS VARCHAR (9) )

Sorry if I haven't read your question correctly - 9:15 is far too early in
the morning ;-)

Rowland.