how to use update query in SQL.

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Dear Team,

I have one table .
Table Name: STDCodeTable
column are as follow:
ID int
State varchar(10)
Area varchar(10)
STDCode varchar(10)

i have one query .In STDCode Column, i have the value like 2345,4567,478248 ... and so on.
I want 0 before all digits like 02345 and it should be for greater than 4 digit.if the STDCode is greater than 4 digit then add 0 before STDCode.
so what will be my update query???
i have tried this but it updating all the STDCOde :
update STDCodeTable
set STDCode = '0' + STDCode
where STDCode >2

Kindly help.

Thanks
Harshal

Posted 21-Feb-14 0:52am

R Harshal

Add a Solution

3 solutions

Solution 1

Google gave me this
Formatting number to add leading zeros - SQL Server[^]
How to pad a numeric type column with leading zeros?[^]

Posted 21-Feb-14 1:22am

thatraja

Comments

R Harshal 21-Feb-14 7:45am

thanks for your quick reply but it does not work for me.
My requirement is i want to place 0 for 4 digit not for all.
Please Guide me..
Thanks
Harshal

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

King Fisher · Accepted Answer · 2014-02-21T01:50:00

Solution 2

SQL

update STDCodeTable
set STDCode = '0' + STDCode
where len(STDCode) >=4

or

use REPLICATE

SQL

update STDCodeTable
set STDCode = Replicate('0',1) +StdCode
where len(STDCode)>=4

Posted 21-Feb-14 1:50am

King Fisher

Updated 21-Feb-14 2:04am

v3

Comments

R Harshal 21-Feb-14 8:37am

Thanks for your reply.
Thank you so much

Peter Leow · Accepted Answer · 2014-02-21T02:02:00

Solution 3

If you want to add '0' to stdcode value that is longer than 4 digits then try this:

SQL

update table1 set stdcode = '0' + stdcode
where len(stdcode) > 4;
select * from table1;

Posted 21-Feb-14 2:02am

Peter Leow

Comments

R Harshal 21-Feb-14 8:35am

Thanks Brother .I realised my mistake..
Thank you so much