Query for the updation of a field

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query for the updation of a field

Author

Topic

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-10-29 : 01:51:54

I have one table RegCode with the fileds SchoolID(NVarchar(6)) and PreCountryID(NVarchar(5))

In My table I have datas like

SchoolID PreCountryID
-----------------------

AB0 1
AB0 23
AB0 114
AB0 2
AB0 1010

Now my reuirement is, I want all values to be updated like

SchoolID PreCountryID
-----------------------

AB0 0001
AB0 0023
AB0 0114
AB0 0002
AB0 1010

it, 4 degits should be there for all the vallues. So, I have to fill it with 0's. I have to do this for the entire table, I mean no need to connect or check the SchoolID, Simply I have to update the PreCountryID field.

Thank you
Ceema

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-10-29 : 01:03:09

update mytable set PreCountryID = replicate('0', 4 - len(PreCountryID)) + PreCountryID

SqlSpec - a fast, cheap, and comprehensive data dictionary generator for
SQL Server 2000/2005 and Analysis Server 2005 - http://www.elsasoft.org

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-29 : 01:43:50

[code]
update mytable
set
PreCountryID = right('0000'+PreCountryID,4)
[/code]

CODO ERGO SUM

ceema
Yak Posting Veteran

80 Posts

Posted - 2006-10-29 : 01:50:41

Hello Michael Valentine Jones,jezemine,

Thank you so much for the reply.

Regards
Ceema

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources