chokka

asked on

# SQL - Update Query with Case Condition

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.

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.

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)

UPDATE YourTable

SET YourColumn = REPLICATE('0', 11 - LEN(CAST(YourColumn AS VARCHAR))) + CAST(YourColumn AS VARCHAR)

ASKER

Name of the Table : BalanceReport

Name of the Column : NDC Varchar(50)

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

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

REPLICATE will repeats the '0' so that total 11 characters will be there in result.

Something like that:

update mytable

set prefix = case LEN(prefix)

when 9 then '00' + prefix

when 8 then '000' + prefix

end

update mytable

set prefix = case LEN(prefix)

when 9 then '00' + prefix

when 8 then '000' + prefix

end

For VARCHAR Column, no need of that CASTing. Try this query

UPDATE YourTable

SET YourColumn = REPLICATE('0', 11 - LEN(YourColumn)) + YourColumn

UPDATE YourTable

SET YourColumn = REPLICATE('0', 11 - LEN(YourColumn)) + YourColumn

ASKER

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.

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.

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

Updated my query

UPDATE BalanceReport

SET NDC = REPLICATE('0', 11 - LEN(NDC)) + NDC

WHERE LEN(NDC) IN (8, 9)

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
```

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

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 .. ?

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 .. ?

chokka:

Did you try my query - http:#34909007 ?

Did you try my query - http:#34909007 ?

ASKER

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.

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.

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

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

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.

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
```

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.