CODEWITHSUNDEEP
sql-serverrecursion
newbieCSharp
newbieCSharp

Reputation: 191

Recursively updating statement in sql server

I have a table with IDs varchar(10). Possible values are like 

create table temp(id varchar(10))
insert into id('01')
insert into id('011')
insert into id('0110')
insert into id('01110')
insert into id('011111')
insert into id('02')
insert into id('020')
insert into id('0222')

and so on .i.e lengths of 2 to 6. Our new requirement says that we have to have decimal after 2 numbers i.e 01 remains same; 011 becomes 011.1; 0110 to 01.10; 01110 to 01.11.0 ;011111 to 01.11.11 I manually wrote update statements but there are 700 such distinct IDs How can I recursively apply a update statement.

Thanks

Upvotes: 0

Views: 54

Answers (2)

Mike
Mike

Reputation: 3831

A quick set-based (non-looping) approach would use a Case statement like this:

Update dbo.Temp
Set id = 
  Case 
  When Len(Id) <= 2 Then Id
  When Len(Id) Between 3 and 4 Then Left(Id, 2)
       + '.' + Substring(Id, 3, 2)
  When Len(Id) Between 5 and 6 Then Left(Id, 2) 
       + '.' + Substring(Id, 3, 2) 
       + '.' + Substring(Id, 6, 2)
  End;

Here's the SQL Fiddle.

Upvotes: 1

Kevin Suchlicki
Kevin Suchlicki

Reputation: 3145

Your question's not too clear, but if I understand correctly, sounds like you want to insert a decimal point after every second number. One way is with a loop:

CREATE TABLE #t(id VARCHAR(100));
INSERT #t (id) VALUES ('01'),('011'),('0110'),('01110'),('011111'),('02'),('020'),('0222'),('010203040');

DECLARE @iteration INT = 0, @rowsAffected INT = 1;

WHILE @rowsAffected > 0
BEGIN
    UPDATE  #t
    SET     id = STUFF(id, 3 + (@iteration * 3), 0, '.')
    WHERE   LEN(id) > 2 + (3 * @iteration) ;

    SELECT  @rowsAffected = @@ROWCOUNT
            , @iteration += 1;
END;

You need to be careful that by adding extra characters into your data, you don't overflow the size of the column.

Upvotes: 0

Related Questions