Solved

SQL - Update Query with Case Condition

Posted on 2011-02-16
16
455 Views
Last Modified: 2012-05-11
SQL 2008 / 2005

I am trying to update a Column with Conditions :- If the Column Value has 9 digits, then Add 2 Zeros on its Prefix, If the Column Value has 8 digits, then Add 3 zeros on its Prefix.

This is an Update Query, - I am not sure, how to fix this issue.

0
Comment
Question by:chokka
  • 6
  • 4
  • 3
  • +3
16 Comments
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 34908896
If you use the Right command, you can achieve what you want:
RIGHT('000000000' + <field>, 9)

If you need to limit it to just fields with 8 or 9 digits, use a where clause that filters for values with lengths of 8 or 9.
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34908912
If the column is of integer datatype, try this query with cast
UPDATE YourTable
SET YourColumn = REPLICATE('0', 11 - LEN(CAST(YourColumn AS VARCHAR))) + CAST(YourColumn AS VARCHAR)
0
 

Author Comment

by:chokka
ID: 34908923


Name of the Table : BalanceReport

Name of the Column : NDC Varchar(50)
0
 
LVL 13

Expert Comment

by:devlab2012
ID: 34908930
It will make the length 9 characters by appending zeros in the beginning.

update TblName set colName = right('000000000' + colName, 9)

It will work if you colName if of varchar type, you cannot prefix zeros in the numeric types
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34908934
REPLICATE will repeats the '0' so that total 11 characters will be there in result.
0
 
LVL 1

Expert Comment

by:Andrei Hetel
ID: 34908939
Something like that:  
update mytable  
 set prefix = case LEN(prefix)
                        when 9 then '00' + prefix
                        when 8 then '000' + prefix
                    end
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34908955
For VARCHAR Column, no need of that CASTing. Try this query
UPDATE YourTable
SET YourColumn = REPLICATE('0', 11 - LEN(YourColumn)) + YourColumn
0
 

Author Comment

by:chokka
ID: 34908980
Rajkumar and Devlab :-

In my Query, i need a condition whether total number of digits are 8 or 9

Based on that i need to add prefix zeros.

total number of digits in that column should be 11.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 8

Expert Comment

by:rushShah
ID: 34909005

update BalanceReport
set NDC =case when len(NDC)=9 then '00' + NDC  when len(NDC )=8 then '000' + NDC

Open in new window

0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34909007
Updated my query

UPDATE BalanceReport
SET NDC = REPLICATE('0', 11 - LEN(NDC)) + NDC
WHERE LEN(NDC) IN (8, 9)


create table #BalanceReport
(
	NDC varchar(50)
)

insert into #BalanceReport
select '12' union
select '1' union
select '12345' union
select '12345678'

UPDATE #BalanceReport
SET NDC = REPLICATE('0', 11 - LEN(NDC)) + NDC 
WHERE LEN(NDC) IN (8, 9)

select * from #BalanceReport

drop table #BalanceReport

Open in new window

0
 
LVL 8

Accepted Solution

by:
rushShah earned 500 total points
ID: 34909008
sorry try this,


update BalanceReport
set NDC =case when len(NDC)=9 then '00' + NDC  when len(NDC )=8 then '000' + NDC end

Open in new window

0
 

Author Comment

by:chokka
ID: 34909123
rushshah,

Thank you.

There is a small correction, i request your help in the Case Statement.

Some Columns already have 11 digits, in that scenario - we need to ignore.
Now the Case Condition runs all the Columns irrespective whether they have 7 or 8 or 9 or 10 or 11 digits.

If they have 7, we need to add 4 zeros
if they have 11, we dont want any zeros.

From your case condition, i am able to modify 7,8,9,10 length.
But for 11 digit length .. ?
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34909160
chokka:
Did you try my query - http:#34909007 ?
0
 

Author Comment

by:chokka
ID: 34909191
Rajkumar, Thank you for helping me.

I have not tried your query. Reason is that i - I am also concerned about QUery Performance.

In your query, you are creating a Temp Table and then Inserting .. Query Cost Estimation would be high.

0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 34909253
Oh man. That was for demonstrating purpose only
I was trying to show you how this query works with sample data
You need to query that I posted first in that comment alone.

Any way - Nice to see you got the solution.
Raj
0
 
LVL 8

Expert Comment

by:rushShah
ID: 34913256
hi chokka,

you can add different length case statements, for length=11, you don't need to add any condition as we don't need to update,

you can try like this,

you can also try Rajkumar's query which does the same thing in better readable format.
update BalanceReport
set NDC =case when len(NDC)=10 then '0' + NDC when len(NDC)=9 then '00' + NDC when len(NDC)=8 then '000' + NDC  when len(NDC )=7 then '0000' + NDC   when len(NDC )=6 then '00000' + NDC end

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now