while updating this kind of scenario exists

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

while updating this kind of scenario exists

Author

Topic

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2012-12-17 : 06:38:16

hello all,

i need to update one column from another column in same table..

nationalID USERID
NULL 'djn dj33 9202vkk dmdmd23'
null 'KMFKF43848 JDJD9 34934903403'

SO I HAVE USED substirng and repalce functions to remove spaces

Upto now its fine but i need to pick only 9 numbers for (1) Id it is giving 9 characters and for other it is giving 11 characters.

so i have written if condition like this

WHILE @Value LIKE '%[^0-9]%'
begin
SET @Value = SUBSTRING(REPLACE(@Value, SUBSTRING(@Value, PATINDEX('%[^0-9]%', @Value), 2), ''),0,20)
-- select @Value
end
if(LEN(@Value)=7)
Begin
update NewFakeList set NationalID=@Value+'74' where ID = @tempcount
end
in second condition if it get 7 characters hardcoding with '74' so it will reach criteria as 9 numbers.Is there any query like for one number i update 74 for another need to update 23 like randomly generating numbers...suggest me

P.V.P.MOhan

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-12-17 : 07:00:52

Not sure what you want - something like this maybe?

if(LEN(@Value)=7)
Begin
update NewFakeList set NationalID=@Value+ right('00' + convert(varchar(2),convert(int,rand()*100)),2)
where ID = @tempcount
end

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2012-12-17 : 08:16:15

Simply one thing when i update there is difference in string In one row and another row like atlast i need to get 9 digits like 1) 373737373 and 2)2265272 u can observe 1st row have satisfied my criteria and second row have 7 digits so iused this query
update NewFakeList set NationalID=@Value+'74' where ID = @tempcount

but it will update 74 for all 7 digits.i need to know i can randomly update last 2 digits like 74,23,67 etc etc

P.V.P.MOhan

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-12-17 : 08:25:22

No - it will update the row that you have checked. The code I have given will update with a random(ish) 2 digits.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2012-12-17 : 08:56:27

can u please help me out writing query for my requirement :

'jdjd383 dkjd 39303 jsdjd' have to remove spaces and alphabets only need to pick 9 digits if 7 digits came need to genrate random numbers for last 2 places

declare @value varchar(200)

@Value = SUBSTRING(REPLACE(@Value, SUBSTRING(@Value, PATINDEX('%[^0-9]%', @Value), 2), ''),0,20)

update mohanfuture set givenname=@Value+right('00' + convert(varchar(2),convert(int,rand()*100)),2)

can u please help me out merging both the statements

P.V.P.MOhan

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-12-17 : 09:05:07

WHILE @Value LIKE '%[^0-9]%'
begin
SET @Value = REPLACE(@Value, SUBSTRING(@Value, PATINDEX('%[^0-9]%', @Value), 1), '')
end
if(LEN(@Value)=7)
Begin
select @Value = @Value+ right('00' + convert(varchar(2),convert(int,rand()*100)),2)
end
update NewFakeList set NationalID=@Value where ID = @tempcount and NationalID<>@Value

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources