Update SQL Data using mask and rules

Question

I have about 3000 entries in a column in SQL 2012 which are unstructured at the moment ie

1.1.01.10, 1.1.1.11

I want to get the data into a format which includes a leading 0 for all single numbers i.e.

01.01.01.10 and so on.

is there any way of doing this with an update query? I can do this by exporting to excel and manipulating there but I want to avoid this if possible.

Is the data always going to be in same format i.e. the maximum length string possible is, say, 11.11.11.11? — shree.pat18, Jul 02 '14 at 09:20
Can u check the function and update the same in u r table and let meknow wether it is working or not — Azar, Jul 02 '14 at 10:09
Shree no the string length can run to three integers between . s unfortunately — MJM, Jul 03 '14 at 10:21
Azar - going to try it all out now and will let you know, awesome — MJM, Jul 03 '14 at 10:22

Azar · Answer 1 · 2014-07-02T10:32:03.810

Alter function Pad
(
    @str varchar(max) 
)
returns varchar(max)
as
begin
Declare @nstr varchar(max)

while(PATINDEX('%.%',@str)<>0)
  begin
    Set @nstr = isnull(@nstr,'')+case when PATINDEX('%.%',@str) = 2 then '0'+substring(@str,PATINDEX('%.%',@str)-1,1) else SUBSTRING(@str,1,PATINDEX('%.%',@str)-1) end+'.'
    Set @str = case when PATINDEX('%.%',@str) = 2 then stuff(@str,PATINDEX('%.%',@str)-1,2,'') else stuff(@str,1,PATINDEX('%.%',@str),'') end 
  end
Set @nstr = isnull(@nstr,'')+case when len(@str) <> 1 then @str when len(@str) = 1 then '0'+@str else '' end 
return @nstr
end



update t
set num = [dbo].pad(num)
from table t

Serpiton · Answer 2 · 2014-07-02T12:27:30.770

If the data have always 4 block it's possible to break them in single unit one at a time.

With F AS (
  SELECT data
       , rem = substring(data, patindex('%.%', data) + 1, len(data))
       , value1 = substring(data, 1, patindex('%.%', data) - 1)
  FROM   Table1
), S AS (
  SELECT data
       , rem = substring(rem, patindex('%.%', rem) + 1, len(rem))
       , value1
       , value2 = substring(rem, 1, patindex('%.%', rem) - 1)
  FROM   F
), T AS (
  SELECT data
       , value1
       , value2
       , value3 = substring(rem, 1, patindex('%.%', rem) - 1)
       , value4 = substring(rem, patindex('%.%', rem) + 1, len(rem))
  FROM   S
)
UPDATE T SET  
       Data = CONCAT(RIGHT('00' + value1, 2), '.'
                   , RIGHT('00' + value2, 2), '.'
                   , RIGHT('00' + value3, 2), '.'
                   , RIGHT('00' + value4, 2));

SQLFiddle Demo

the query can be made smaller, but losing readability.

If the number of block are unknown and/or can change between rows, the query is more complex and involve a recursive CTE

With Splitter AS (
  -- anchor
  SELECT data
       , rem = substring(data, patindex('%.%', data) + 1, len(data))
       , pos = len(data) - len(replace(data, '.', '')) + 1
       , value = substring(data, 1, patindex('%.%', data) - 1)
       , res = CAST('' as nvarchar(50))
  FROM   Table1
  UNION ALL
  -- runner
  SELECT data
       , rem = substring(rem, patindex('%.%', rem) + 1, len(rem))
       , pos = pos - 1
       , value = substring(rem, 1, patindex('%.%', rem) - 1)
       , res = CAST(res + RIGHT('00' + value, 2) + '.' as nvarchar(50))
  FROM   Splitter
  WHERE  patindex('%.%', rem) > 1
  UNION ALL
  -- stop
  SELECT data
       , rem = ''
       , pos = pos - 1
       , value = rem
       , res = CAST(res + RIGHT('00' + value, 2) 
                  + '.' + RIGHT('00' + rem, 2) as nvarchar(50))
  FROM   Splitter
  WHERE  patindex('%.%', rem) = 0
    AND  rem <> ''
)
UPDATE table1 Set
  Data = res
FROM   table1 t
       INNER JOIN Splitter s ON t.Data = s.Data and s.Pos = 1

SQLFiddle demo

The anchor query of the CTE get the first block in value, set pos with the number of block and prepare the result (res).
The runner query works for the following block, but not the last one, searching the nth block and adding blocks to the result.
The stop query get the last block without searching for another dot, that will not find, and complete the constrution of the result. Having set the pos initially to the number of blocks, now it'll be 1.

Update SQL Data using mask and rules

2 Answers2