How to Update following Data [Resolved]

Posted by Jayakumars under ADO.NET on 1/5/2018 | Points: 10 | Views : 186 | Status : [Member] [MVP] | Replies : 3
Hi

My Data like this

S.no Name no
1 AA 852497
2 BB 258
3 CC 6597
4 DD 9747587
5 EE 78850

How to update this

I need number field length based update

i need
ex:
no
852497 -> 111111
258 -> 111
6597 -> 1111

how will do this

Mark as Answer if its helpful to you


Responses

Posted by: Rajnilari2015 on: 1/18/2018 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
@Jayakumar,
Is this what you are looking for

declare @t table (sr_no int identity(1,1),name nvarchar(200),num nvarchar(200))

insert into @t VALUES('AA', '852497'),('BB', '258'),('CC', '6597'),('DD', '9747587'),('EE', '788501')

select *, ValueToUpdate = replicate('1',len(num))
from @t


/* Result */

sr_no	name	num	ValueToUpdate

1 AA 852497 111111
2 BB 258 111
3 CC 6597 1111
4 DD 9747587 1111111
5 EE 788501 111111


For updating the records, use

update @t

set num = replicate('1',len(num))


/* Result */

sr_no	name	num

1 AA 111111
2 BB 111
3 CC 1111
4 DD 1111111
5 EE 111111


--
Thanks & Regards,
RNA Team

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Anujarishimehta on: 1/9/2018 [Member] Starter | Points: 25

Up
0
Down
Please check if this is what needed

create table #test
(
name nvarchar(200),
num nvarchar(200),
sr_no int identity(1,1)
)
insert into #teSt VALUES(

'AA', '852497'),
('BB', '258'),
('CC', '6597'),
('DD', '9747587'),
('EE', '788501' )

WHILE EXISTS (SELECT TOP 1 * FROM #teSt WHERE PATINDEX('%[2-9]%',NUM)> 0)
UPDATE #teSt
SET NUM = REPLACE(NUM,SUBSTRING(NUM,PATINDEX('%[2-9]%',NUM),1),'1')

UPDATE #teSt
SET NUM = REPLACE(NUM,'0','1')


SELECT * FROM #teSt

Regards,

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 1/18/2018 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi

Neiljakson

I think your post recent all my questions this is not right.

if you need promotion post separately Mr.Neiljakson

becos your reply not useful for me

Mark as Answer if its helpful to you

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response