Update multiple columns with Different Values

Question

               I have table following this format :

Id   BillNo:      Voucher No:
1   W2015-16/0001  W2015-16/0001
2   W2015-16/0002  W2015-16/0002
3   W2015-16/0003  W2015-16/0003
4   W2015-16/0004  W2015-16/0004
5   W2015-16/0005  W2015-16/0005
6   W2015-16/0006  W2015-16/0006
7   W2015-16/0007  W2015-16/0007
8   W2015-16/0008  W2015-16/0008
9   W2015-16/0009 W2015-16/0009
10   W2015-16/0010  W2015-16/0010

But Instead of this Format: Now I want to Update All "Voucher No" and "Bill No" below on this format :

Id   BillNo:      Voucher No:
1   W0001/2015-16 W0001/2015-16
2   W0002/2015-16 W0002/2015-16
3   W0003/2015-16 W0003/2015-16
4   W0004/2015-16 W0004/2015-16
5   W0005/2015-16 W0005/2015-16
6   W0006/2015-16 W0006/2015-16
7   W0007/2015-16 W0007/2015-16
8   W0008/2015-16 W0008/2015-16
9   W0009/2015-16 W0009/2015-16
10  W0010/2015-16 W0010/2015-16
11  W0011/2015-16 W0011/2015-16

like this i have a 1000+ records i should update , But i don't know is this possible to do in Lesser time, Kindly give your suggestion , i am new to SQL Thanks Advance

please don't tag irrelevant products. how is mysql and oracle used here? — ughai
– ughai, Commented May 26, 2015 at 11:35
Question -> Only First CHAR is always a constant value or can expect more values in first place? W2015 .. can it be WER2015? — user2066540
– user2066540, Commented May 26, 2015 at 11:45

Gordon Linoff · Accepted Answer · 2015-05-26 11:38:28Z

1

Assuming everything is in a fixed format, then you would just use update and string manipulations:

update table t
    set billno = left(billno, 1) + right(billno, 4) + '/' + substring(billno, 2, 7),
        VoucherNo = left(VoucherNo, 1) + right(VoucherNo, 4) + '/' + substring(VoucherNo, 2, 7);

answered May 26, 2015 at 11:38

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nenad Zivkovic · Accepted Answer · 2015-05-26 11:44:00Z

0

UPDATE YourTable
SET BillNo = SUBSTRING(BillNo,1,1) + SUBSTRING(BillNo,10,4) + '/' + SUBSTRING(BillNo,2,7)
, VoucherNo = SUBSTRING(VoucherNo,1,1) + SUBSTRING(VoucherNo,10,4) + '/' + SUBSTRING(VoucherNo,2,7)

Test:

DECLARE @a NVARCHAR(max) = 'W2015-16/0001' 
SELECT SUBSTRING(@a,1,1) +
SUBSTRING(@a,10,4) + '/' + SUBSTRING(@a,2,7)

answered May 26, 2015 at 11:44

Nenad Zivkovic

18.6k6 gold badges45 silver badges58 bronze badges

3 Comments

Felish Anand Over a year ago

It's Awesome ,, Really very thank full, Giving Ur Code is Working Fine and greatful @Nenad Zivkovic

Felish Anand Over a year ago

Thanks for All helping my problem ;)

Nenad Zivkovic Over a year ago

@FelishAnand Sure, no problem: Substring function splits your string into parts. SUBSTRING(BillNo,1,1) takes 1 character from position 1, which is W in your example. SUBSTRING(BillNo,2,7) is 7 characters starting from position 2 = 2015-16, and SUBSTRING(BillNo,10,4) is 4 characters from 10th position which is 0001... So basically you split your input string into parts and reassemble it in different order.

user2066540 · Accepted Answer · 2015-05-26 12:07:47Z

0

There is no straight forward way to achieve this. You may need to define some patterns in SQL to replace and play with all string functions. Here is

declare @name varchar(50) ='W2015-16/0001';
select SUBSTRING(STUFF(@name,2,0,(SUBSTRING(@name,CHARINDEX('/',@name) + 1,len(@name)) + '/')),0,14)
--W0001/2015-16

This gives you the result that you are looking for. So you need to set this into your update statement by joining with the table by ID. This would work only if the pattern you have given is correct.

answered May 26, 2015 at 12:07

user2066540

3572 gold badges6 silver badges16 bronze badges

Collectives™ on Stack Overflow

Update multiple columns with Different Values

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related