Обновите данные SQL, используя маску и правила

Question

У меня есть около 3000 записей в столбце в SQL 2012, которые на данный момент неструктурированы, т.е.

1.1.01.10, 1.1.1.11

Я хочу получить данные в формате, который включает в себя ведущий 0 для всех отдельных чисел, т.е.

01.01.01.10 и так далее.

Есть ли способ сделать это с помощью запроса на обновление? Я могу сделать это, экспортируя в Excel и манипулируя там, но я хочу избежать этого, если это возможно.

Answer 1

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

Answer 2

Если данные всегда имеют 4 блока, их можно разбить на один блок по одному.

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

запрос может быть сделан меньше, но теряет читабельность.

Если номер блока неизвестен и / или может меняться между строками, запрос является более сложным и включает в себя рекурсивный 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

Якорный запрос CTE получить первый блок в value, задавать pos с номером блока и подготовить результат (res).
Запрос бегуна работает для следующего блока, но не для последнего, ищет n-й блок и добавляет блоки к результату.
Стоп запрос получает последний блок без поиска другой точки, которая не найдет, и завершает построение результата. Установив pos Первоначально на количество блоков, теперь это будет 1.