Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Поиск и обновление всех вхождений подстроки в строку  [new]
John__Smit
Member

Откуда:
Сообщений: 10
Добрый день. Работаю с Microsoft SQL Server 2005.
Есть таблица вида:
ID DATA FIN_PER
1 a 1:58.57,2:58.59
2 b 10:5.25,11:6.40
3 c 111:8.29,112:7.11,113:4.41
4 d 12:16.25

в которой столбец FIN_PER представляет собой остаток средств по периодам.
То что находится перед символом ":" это период (который может быть представлен одним,двумя,тремя знаками)
То что находится после символа ":" до символа "," либо до конца строки - это собственно остаток средств
Периодов в строке может быть очень много (грубо до 100 периодов)

Задача прибавить к каждому периоду @x значение, например 3, чтобы получилось
ID DATA FIN_PER
1 a 4:58.57,5:58.59
2 b 13:5.25,14:6.40
3 c 114:8.29,115:7.11,116:4.41
4 d 15:16.25

Ковырялся с substring,charindex,patindex но результата так и не добился, очень сложно.
Подскажите как это сделать? (Желательно без перебора каждой строки таблицы через курсор, потому что строк в таблице очень много)

Сообщение было отредактировано: 18 ноя 21, 16:37
18 ноя 21, 16:32    [22397583]     Ответить | Цитировать Сообщить модератору
 Re: Поиск и обновление всех вхождений подстроки в строку  [new]
Oleg_SQL
Member

Откуда: от туда
Сообщений: 241
John__Smit,

string_split бы здесь был полезен...

Написать свою функцию или (гораздо лучше) поменять формат хранения в таблице на нормальный.

ID DATA  PERIOD BALANCE
1       a  1             58.57
2       a  2             58.59
...
18 ноя 21, 16:47    [22397595]     Ответить | Цитировать Сообщить модератору
 Re: Поиск и обновление всех вхождений подстроки в строку  [new]
Агрох
Member

Откуда:
Сообщений: 135
John__Smit
(грубо до 100 периодов)


Грубо говоря или точно до 100?

Рекурсией раскладываешь составные строки ("10:15.1, 15:44.44") на отдельные ("10:15.1" и "15:44.44").
С каждой отдельной строкой делаешь тёмное дело.
Склеиваешь их обратно в одну общую, по общему ID.
Апдейтишь таблицу новыми значениями.
18 ноя 21, 16:50    [22397598]     Ответить | Цитировать Сообщить модератору
 Re: Поиск и обновление всех вхождений подстроки в строку  [new]
John__Smit
Member

Откуда:
Сообщений: 10
Агрох

Грубо говоря или точно до 100?


До 100, до 200 не имеет значения, очень много. (В таблице 32 млн записей, я не проверял каждую)
(Апдейтить я буду тысяч 50 записей)

Агрох

Рекурсией раскладываешь составные строки ("10:15.1, 15:44.44") на отдельные ("10:15.1" и "15:44.44").
С каждой отдельной строкой делаешь тёмное дело.
Склеиваешь их обратно в одну общую, по общему ID.
Апдейтишь таблицу новыми значениями.


Это понятно. Можно чуть подробнее как в моей ситуации это сделать?
Сложность в том что у меня периоды могут задаваться разным числом знаков, я пытался писать скрипт по поиску от запятой до двоеточия, запутался. Наверняка есть красивый код который это делает.

Сообщение было отредактировано: 18 ноя 21, 16:57
18 ноя 21, 16:55    [22397605]     Ответить | Цитировать Сообщить модератору
 Re: Поиск и обновление всех вхождений подстроки в строку  [new]
Gerasimenko
Member

Откуда:
Сообщений: 4705
John__Smit,

create table #t(id int, [data] varchar(20), FIN_PER varchar(50))

insert into #t select 1, 'a', '4:58.57,5:58.59'
insert into #t select 2, 'b', '13:5.25,14:6.40'
insert into #t select 3, 'c', '114:8.29,115:7.11,116:4.41'
insert into #t select 4, 'd', '15:16.25'

select id, data, FIN_PER, 
  CASE CHARINDEX(',', FIN_PER) 
    WHEN 0 THEN FIN_PER
	ELSE SUBSTRING(FIN_PER, 1, CHARINDEX(',', FIN_PER)-1) 
  END AS PartOne,
  CASE CHARINDEX(',', FIN_PER) 
    WHEN 0 THEN ''
	ELSE SUBSTRING(FIN_PER, CHARINDEX(',', FIN_PER)+1, (LEN(FIN_PER)-CHARINDEX(',', FIN_PER)))  
  END AS PartTwo
  from #t


drop table #t


Допилишь, используя еще CAST
18 ноя 21, 16:59    [22397607]     Ответить | Цитировать Сообщить модератору
 Re: Поиск и обновление всех вхождений подстроки в строку  [new]
Oleg_SQL
Member

Откуда: от туда
Сообщений: 241
John__Smit,

32 млн записей...

А все же, зачем хранить данные в таком виде? Чтобы писать километры кода для ковыряния в нём?
Может проще 1 раз сесть и навести порядок?
18 ноя 21, 17:01    [22397609]     Ответить | Цитировать Сообщить модератору
 Re: Поиск и обновление всех вхождений подстроки в строку  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 9155
John__Smit,

в колонке всегда должны находиться атомарные значение, то есть те, которые нельзя логически разбить на строки. Если у вас не так, то вам надо выполнить такое разделение, добавив дополнительные колонки и скорректировать п/о для заполнения и выборки.
Иначе ваша СУБД превращается в эксель.
18 ноя 21, 17:44    [22397635]     Ответить | Цитировать Сообщить модератору
 Re: Поиск и обновление всех вхождений подстроки в строку  [new]
Gerasimenko
Member

Откуда:
Сообщений: 4705
Gerasimenko
John__Smit,

create table #t(id int, [data] varchar(20), FIN_PER varchar(50))

insert into #t select 1, 'a', '4:58.57,5:58.59'
insert into #t select 2, 'b', '13:5.25,14:6.40'
insert into #t select 3, 'c', '114:8.29,115:7.11,116:4.41'
insert into #t select 4, 'd', '15:16.25'

select id, data, FIN_PER, 
  CASE CHARINDEX(',', FIN_PER) 
    WHEN 0 THEN FIN_PER
	ELSE SUBSTRING(FIN_PER, 1, CHARINDEX(',', FIN_PER)-1) 
  END AS PartOne,
  CASE CHARINDEX(',', FIN_PER) 
    WHEN 0 THEN ''
	ELSE SUBSTRING(FIN_PER, CHARINDEX(',', FIN_PER)+1, (LEN(FIN_PER)-CHARINDEX(',', FIN_PER)))  
  END AS PartTwo
  from #t


drop table #t


Допилишь, используя еще CAST
не увидел, что переменное число.... Можно с помощью bulkinsert попробовать сначала разбить на столбцы
18 ноя 21, 18:02    [22397647]     Ответить | Цитировать Сообщить модератору
 Re: Поиск и обновление всех вхождений подстроки в строку  [new]
Агрох
Member

Откуда:
Сообщений: 135
WITH R0 AS (
SELECT 1 AS ID, '10:15.1,16:11.22,55:11.2' AS val
UNION
SELECT 2 AS ID, '1:55.55,2:26.05' AS val
UNION
SELECT 3 AS ID, '4:23.1' AS val
), R1 AS (
SELECT ID, 
  SUBSTRING(val, 1, CHARINDEX(':', val) - 1) AS val1, 
  CASE 
  	WHEN CHARINDEX(',', val) > 0 THEN SUBSTRING(val, CHARINDEX(':', val) + 1, CHARINDEX(',', val) - CHARINDEX(':', val) - 1)
  	ELSE SUBSTRING(val, CHARINDEX(':', val) + 1, len(val) - CHARINDEX(':', val))
  END AS val2, 
  CASE 
  	WHEN CHARINDEX(',', val) > 0 THEN SUBSTRING(val, CHARINDEX(',', val) + 1, len(val))
  	ELSE ''
  END as val
FROM R0
WHERE val <> ''
UNION all
SELECT ID, 
  SUBSTRING(val, 1, CHARINDEX(':', val) - 1) AS val1, 
  CASE 
  	WHEN CHARINDEX(',', val) > 0 THEN SUBSTRING(val, CHARINDEX(':', val) + 1, CHARINDEX(',', val) - CHARINDEX(':', val) - 1)
  	ELSE SUBSTRING(val, CHARINDEX(':', val) + 1, len(val) - CHARINDEX(':', val))
  END AS val2, 
  CASE 
  	WHEN CHARINDEX(',', val) > 0 THEN SUBSTRING(val, CHARINDEX(',', val) + 1, len(val))
  	ELSE ''
  END as val
FROM R1
WHERE val <> ''
), R2 AS (
SELECT
  ID,
  CAST(CAST(val1 AS int) + 5 AS VARCHAR(250)) AS val1,
  val2
FROM R1
)
SELECT * FROM R2 ORDER BY ID;


Объединять по моему через FOR XML PATH(''), но подзабыл уже как точно делать. Можно и через оконные попробовать или опять рекурсией.

P/S В "CAST(CAST(val1 AS int) + 5 AS VARCHAR(250)) AS val1," + 5 это то самое прибавление.

Сообщение было отредактировано: 18 ноя 21, 18:07
18 ноя 21, 18:06    [22397651]     Ответить | Цитировать Сообщить модератору
 Re: Поиск и обновление всех вхождений подстроки в строку  [new]
invm
Member

Откуда: Москва
Сообщений: 9913
declare @t table (id int, data varchar(20), FIN_PER varchar(50));

insert into @t select 1, 'a', '4:58.57,5:58.59';
insert into @t select 2, 'b', '13:5.25,14:6.40';
insert into @t select 3, 'c', '114:8.29,115:7.11,116:4.41';
insert into @t select 4, 'd', '15:16.25';

declare @x numeric(18,2) = 10;

select
 t.*, stuff(b.x.value('.', 'varchar(max)'), 1, 1, '')
from
 @t t cross apply
 (select cast('<item per = "' + replace(replace(t.FIN_PER, ':', '" rest = "'), ',', '"/><item per = "') + '"/>' as xml)) a(x) cross apply
 (
  select
   ',' + b.n.value('@per', 'varchar(10)') + ':' + cast(b.n.value('@rest', 'numeric(18,2)') + @x as varchar(30))
  from
   a.x.nodes('/item') b(n)
  for xml path(''), type
 ) b(x);
18 ноя 21, 20:58    [22397691]     Ответить | Цитировать Сообщить модератору
 Re: Поиск и обновление всех вхождений подстроки в строку  [new]
John__Smit
Member

Откуда:
Сообщений: 10
Господа спасибо всем, но я лишь маленький винтик в системе. Понятно что структура хранения "г", но на неё заточено куча хранимых процедур. И даже если я предложу переделать её (и предположим даже нигде не накосячу), то большие дяди и тёти меня всё равно даже слушать не будут.

Уважаемый invm, ваш вариант почти идеален (хоть я пока и не понимаю как это работает), вот только прибавлять @x мне нужно к периоду а не к остаткам, и что то я пока не понимаю как это допилить
19 ноя 21, 08:39    [22397779]     Ответить | Цитировать Сообщить модератору
 Re: Поиск и обновление всех вхождений подстроки в строку  [new]
John__Smit
Member

Откуда:
Сообщений: 10
Разобрался, допилил. Прочитал документацию по использованию методов nodes, value, предложения CROSS APPLY, вспомнил замечательную функцию stuff.

Уважаемый invm спасибо вам огромное, просто спасли меня (дай бог здоровья!). Постараюсь освоить данные методы в дальнейшем.
19 ноя 21, 10:47    [22397842]     Ответить | Цитировать Сообщить модератору
 Re: Поиск и обновление всех вхождений подстроки в строку  [new]
invm
Member

Откуда: Москва
Сообщений: 9913
John__Smit
не понимаю как это допилить
   ',' + cast(b.n.value('@per', 'int') + @x as varchar(10)) + ':' + b.n.value('@rest', 'varchar(30)')
19 ноя 21, 10:53    [22397847]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить