How to Update following Data [Resolved]

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Kumaraspcode2009@gmail.com

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	@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

@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

0	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

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

0	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 Kumaraspcode2009@gmail.com Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts