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

Откуда: Новосибирск
Сообщений: 659
Здравствуйте!

В ханимую процедуру передается строка вида
'{10000000000027578:40000000000000260}%{10000000000092144:%}%{10000000000092215:40000000000000207}'
1. Элементы ограничены фигурными скобками {} и отделяются друг от друга символом "%"
2. Внутри элемента значения разделены символом ":"
3. Левая часть часть элемента есть всегда (тип параметра), правая (значение параметра) - может быть, а может и не быть (%)
4. Все значения фиксированной длины, numeric(18,0), строка собирается из идентификаторов строк в разных таблицах.
5. Количество элементов в строке произвольное, в разумных пределах. Сейчас максимально 4, сделаем с запасом 6

Нужно получить таблицу
тип параметра значение параметра
1000000000002757840000000000000260
1000000000009221540000000000000207

Только те элементы, у которых есть и тип, и значение

Я навял вот такой вывих мозга
+ код

DECLARE @s VARCHAR(255)
SELECT @s = '{10000000000092169:%}%{10000000000092215:%}%{30000000003449513:10000000000093842}'
SELECT @s = REPLACE(@s, '}%{', '}{')

SELECT SUBSTRING(SUBSTRING(@s, s, l), 2, 17) AS subconto_type_id
      ,SUBSTRING(SUBSTRING(@s, s, l), 20, 17) AS item_id
    FROM (SELECT DISTINCT
                 1                                                                                   AS s
                ,CONVERT(INT, REPLACE(REPLACE(CONVERT(VARCHAR(2), t0.b), '1', '37'), '0', '21'))     AS l
              FROM (SELECT 0 AS b UNION ALL SELECT 1 AS b) t0
          UNION ALL
          SELECT DISTINCT
                 1 + CONVERT(INT, REPLACE(REPLACE(CONVERT(VARCHAR(2), t0.b), '1', '37'), '0', '21')) AS s
                ,CONVERT(INT, REPLACE(REPLACE(CONVERT(VARCHAR(2), t1.b), '1', '37'), '0', '21'))     AS l
              FROM (SELECT 0 AS b UNION ALL SELECT 1 AS b) t0
                   CROSS JOIN (SELECT 0 AS b UNION ALL SELECT 1 AS b) t1
          UNION ALL
          SELECT DISTINCT
                 1 + CONVERT(INT, REPLACE(REPLACE(CONVERT(VARCHAR(2), t0.b), '1', '37'), '0', '21'))
                   + CONVERT(INT, REPLACE(REPLACE(CONVERT(VARCHAR(2), t1.b), '1', '37'), '0', '21')) AS s
                ,CONVERT(INT, REPLACE(REPLACE(CONVERT(VARCHAR(2), t2.b), '1', '37'), '0', '21'))     AS l
              FROM (SELECT 0 AS b UNION ALL SELECT 1 AS b) t0
                   CROSS JOIN (SELECT 0 AS b UNION ALL SELECT 1 AS b) t1
                   CROSS JOIN (SELECT 0 AS b UNION ALL SELECT 1 AS b) t2
          UNION ALL
          SELECT DISTINCT
                 1 + CONVERT(INT, REPLACE(REPLACE(CONVERT(VARCHAR(2), t0.b), '1', '37'), '0', '21'))
                   + CONVERT(INT, REPLACE(REPLACE(CONVERT(VARCHAR(2), t1.b), '1', '37'), '0', '21'))
                   + CONVERT(INT, REPLACE(REPLACE(CONVERT(VARCHAR(2), t2.b), '1', '37'), '0', '21')) AS s
                ,CONVERT(INT, REPLACE(REPLACE(CONVERT(VARCHAR(2), t3.b), '1', '37'), '0', '21'))     AS l
              FROM (SELECT 0 AS b UNION ALL SELECT 1 AS b) t0
                   CROSS JOIN (SELECT 0 AS b UNION ALL SELECT 1 AS b) t1
                   CROSS JOIN (SELECT 0 AS b UNION ALL SELECT 1 AS b) t2
                   CROSS JOIN (SELECT 0 AS b UNION ALL SELECT 1 AS b) t3
                    UNION ALL
          SELECT DISTINCT
                 1 + CONVERT(INT, REPLACE(REPLACE(CONVERT(VARCHAR(2), t0.b), '1', '37'), '0', '21'))
                   + CONVERT(INT, REPLACE(REPLACE(CONVERT(VARCHAR(2), t1.b), '1', '37'), '0', '21'))
                   + CONVERT(INT, REPLACE(REPLACE(CONVERT(VARCHAR(2), t3.b), '1', '37'), '0', '21')) AS s
                ,CONVERT(INT, REPLACE(REPLACE(CONVERT(VARCHAR(2), t4.b), '1', '37'), '0', '21'))     AS l
              FROM (SELECT 0 AS b UNION ALL SELECT 1 AS b) t0
                   CROSS JOIN (SELECT 0 AS b UNION ALL SELECT 1 AS b) t1
                   CROSS JOIN (SELECT 0 AS b UNION ALL SELECT 1 AS b) t2
                   CROSS JOIN (SELECT 0 AS b UNION ALL SELECT 1 AS b) t3
                   CROSS JOIN (SELECT 0 AS b UNION ALL SELECT 1 AS b) t4
          UNION ALL
          SELECT DISTINCT
                 1 + CONVERT(INT, REPLACE(REPLACE(CONVERT(VARCHAR(2), t0.b), '1', '37'), '0', '21'))
                   + CONVERT(INT, REPLACE(REPLACE(CONVERT(VARCHAR(2), t1.b), '1', '37'), '0', '21'))
                   + CONVERT(INT, REPLACE(REPLACE(CONVERT(VARCHAR(2), t3.b), '1', '37'), '0', '21'))
                   + CONVERT(INT, REPLACE(REPLACE(CONVERT(VARCHAR(2), t4.b), '1', '37'), '0', '21')) AS s
                ,CONVERT(INT, REPLACE(REPLACE(CONVERT(VARCHAR(2), t5.b), '1', '37'), '0', '21'))     AS l
              FROM (SELECT 0 AS b UNION ALL SELECT 1 AS b) t0
                   CROSS JOIN (SELECT 0 AS b UNION ALL SELECT 1 AS b) t1
                   CROSS JOIN (SELECT 0 AS b UNION ALL SELECT 1 AS b) t2
                   CROSS JOIN (SELECT 0 AS b UNION ALL SELECT 1 AS b) t3
                   CROSS JOIN (SELECT 0 AS b UNION ALL SELECT 1 AS b) t4
                   CROSS JOIN (SELECT 0 AS b UNION ALL SELECT 1 AS b) t5
                    UNION ALL
          SELECT DISTINCT
                 1 + CONVERT(INT, REPLACE(REPLACE(CONVERT(VARCHAR(2), t0.b), '1', '37'), '0', '21'))
                   + CONVERT(INT, REPLACE(REPLACE(CONVERT(VARCHAR(2), t1.b), '1', '37'), '0', '21'))
                   + CONVERT(INT, REPLACE(REPLACE(CONVERT(VARCHAR(2), t3.b), '1', '37'), '0', '21'))
                   + CONVERT(INT, REPLACE(REPLACE(CONVERT(VARCHAR(2), t4.b), '1', '37'), '0', '21'))
                   + CONVERT(INT, REPLACE(REPLACE(CONVERT(VARCHAR(2), t5.b), '1', '37'), '0', '21')) AS s
                ,CONVERT(INT, REPLACE(REPLACE(CONVERT(VARCHAR(2), t5.b), '1', '37'), '0', '21'))     AS l
              FROM (SELECT 0 AS b UNION ALL SELECT 1 AS b) t0
                   CROSS JOIN (SELECT 0 AS b UNION ALL SELECT 1 AS b) t1
                   CROSS JOIN (SELECT 0 AS b UNION ALL SELECT 1 AS b) t2
                   CROSS JOIN (SELECT 0 AS b UNION ALL SELECT 1 AS b) t3
                   CROSS JOIN (SELECT 0 AS b UNION ALL SELECT 1 AS b) t4
                   CROSS JOIN (SELECT 0 AS b UNION ALL SELECT 1 AS b) t5
                   CROSS JOIN (SELECT 0 AS b UNION ALL SELECT 1 AS b) t6
          ) q
    WHERE 1 = 1
      AND (LEFT(SUBSTRING(@s, s, l), 1) = '{'
      AND RIGHT(SUBSTRING(@s, s, l), 1) = '}')
      AND SUBSTRING(SUBSTRING(@s, s, l), 20, 18) <> '%}'

Наверняка можно придумать что-нибудь другое, по проще...

PS Можно не только временную таблицу, но и табличную функцию, потому что все это будет использоваться в запросах вида
+ использование

CREATE TABLE #t(
    subconto_type_id NUMERIC(18, 0) NOT NULL
   ,item_id          NUMERIC(18, 0) NULL
)

INSERT INTO #t(subconto_type_id, item_id)
          SELECT 30000000003449513, 10000000000093842
UNION ALL SELECT 10000000000092215, 40000000001533976

DECLARE @cnt INT
SELECT @cnt = COUNT(*)
    FROM #t

SELECT SUM(ol.sum_bal * ol.deb_or_cred)
    FROM t_oper_list ol
    WHERE 1 = 1
      AND ol.op_date >= @date_b
      AND ol.op_date < DATEADD(dd, 1, @date_e)
      AND @cnt = (SELECT COUNT(*)
                      FROM t_oper_subconto os
                           INNER JOIN #t t ON t.subconto_type_id = os.subconto_type_id
                                     AND t.item_id = os.item_id
                      WHERE os.id = ol.id)

PPS Сейчас версия сервера 2000. Через некоторое время, надеюсь небольшое, будет 2008. Принимаются любые варианты.
13 апр 11, 14:11    [10512513]     Ответить | Цитировать Сообщить модератору
 Re: Парсинг строки  [new]
Yudzhin
Member

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

Напиши CLR функцию и используй Regular Expressions :)
13 апр 11, 14:37    [10512764]     Ответить | Цитировать Сообщить модератору
 Re: Парсинг строки  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
Какая ещё функция?!
DECLARE @S VARCHAR(256);
SET @S='{10000000000027578:40000000000000260}%{10000000000092144:%}%{10000000000092215:40000000000000207}';

SELECT
 SUBSTRING(@S,number+1, CHARINDEX(':',@S,number)-number-1)[1],
 SUBSTRING
 (
  @S,
  CHARINDEX(':',@S,number)+1,
  CHARINDEX('}',@S, CHARINDEX(':',@S,number))-CHARINDEX(':',@S,number)-1
 )[2]
FROM master.dbo.spt_values V
WHERE V.type='P' AND SUBSTRING(@S,number,1)='{'
  AND SUBSTRING(@S, CHARINDEX(':',@S,number)+1,1)<>'%';
13 апр 11, 14:52    [10512891]     Ответить | Цитировать Сообщить модератору
 Re: Парсинг строки  [new]
Anddros
Member

Откуда:
Сообщений: 1077
DECLARE @s VARCHAR(255)
SELECT @s = '{10000000000092169:%}%{10000000000092215:%}%{30000000003449513:10000000000093842}'

declare @s1 xml
select @s1=replace(replace(replace(replace(@s,'}','</c>'),'{','<c>'),'%',''),':','.n')
select parsename(s,2),substring(parsename(s,1),2,8000)
from @s1.nodes('/c') x(y)
cross apply(select x.y.value('.', 'varchar(50)')s)t
where right(s,1)<>'n'

Хмм... Интересно, что Аладдин делает в Новосибе???
13 апр 11, 15:00    [10512958]     Ответить | Цитировать Сообщить модератору
 Re: Парсинг строки  [new]
Anddros
Member

Откуда:
Сообщений: 1077
Забыл дописать: мой вариант для 2005-го и выше
13 апр 11, 15:01    [10512966]     Ответить | Цитировать Сообщить модератору
 Re: Парсинг строки  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
внесу свои пять копеек для sql2000

DECLARE @hDoc INT
DECLARE @str varchar(1000)
set @str='{10000000000027578:40000000000000260}%{10000000000092144:%}%{10000000000092215:40000000000000207}'
SET @str=replace (@str,'%','')
SET @str=replace (@str,'{','<STR str="')
SET @str=replace (@str,'}','"/>')
SET @str='<ROOT>'+@str+'</ROOT>'
SELECT @str
EXEC sp_xml_preparedocument @hDoc OUTPUT, @str

SELECT * FROM openxml(@hDoc, '//')
WITH
(txt varchar(100) '@str')


EXEC sp_xml_removedocument @hDoc
13 апр 11, 15:02    [10512972]     Ответить | Цитировать Сообщить модератору
 Re: Парсинг строки  [new]
Guf
Member

Откуда: Новосибирск
Сообщений: 659
iap,
О, спасибо! На тебя в основном и расчитывал. Забыл как ты этот фокус с таблицей чисел проделывешь... Прочитал статью Массивы и Списки в SQL Server и прицепился к "почти" фиксированной длине.

Anddros,
Тоже что и везде - работает
14 апр 11, 08:10    [10516285]     Ответить | Цитировать Сообщить модератору
 Re: Парсинг строки  [new]
DeColo®es
Member

Откуда: Москва
Сообщений: 5503
Блог
Только использование такого метода работы с XML (а в 2000 другого нет) на нагруженных системах рано или поздно будет приводить к проблемам с памятью.
14 апр 11, 10:11    [10516638]     Ответить | Цитировать Сообщить модератору
 Re: Парсинг строки  [new]
Anddros
Member

Откуда:
Сообщений: 1077
Guf
Anddros,
Тоже что и везде - работает
Пару лет назад он работал исключительно в Москве. :)
14 апр 11, 11:16    [10517089]     Ответить | Цитировать Сообщить модератору
 Re: Парсинг строки  [new]
Guf
Member

Откуда: Новосибирск
Сообщений: 659
Anddros,
+ Это офтоп, конечно, да простят нас модераторы

Я работаю с Аладдином в Новосибирске с 28 мая 2007-го года и, по моим сведениям, он работал здесь года 3-4 до моего прихода.
Такчто Ваши сведения, мягко говоря, не верны.
14 апр 11, 14:03    [10518415]     Ответить | Цитировать Сообщить модератору
 Re: Парсинг строки  [new]
ther
Member

Откуда:
Сообщений: 839
драсте...хотел бы поднять тему и спросить насчет запроса https://www.sql.ru/forum/actualutils.aspx?action=gotomsg&tid=843565&msg=10512891 от товарища iap

в таблице master.dbo.spt_values с type='P' количество в поле number всегда одинаково?
ибо опасно использовать этот запрос если поле будет допустим длинной 3000...но к моей задаче он подходит и меня поле журнала только 1000 символов....просто хотелось бы уточнить что количество number будет железно больше 1000...а пока я переписал сей запрос на использование временной таблицы с заполнением в цикле
15 июл 11, 08:46    [10977656]     Ответить | Цитировать Сообщить модератору
 Re: Парсинг строки  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
ther, вообще-то по совести вместо master..spt_values у вас просто должна быть индексированная таблица чисел. Это самое лучшее решение. Но если вам нужно что-то кому-то побыстрому показать, то ссылаться на master..spt_values вполне нормальный вариант, т.к. эта таблица есть в серваках от 2000 до 2008R2, на счет более ранних и более поздних версий не в курсе, но с большой долей вероятности - тоже там есть.

Опять же, никто не мешает использовать генераторы на CTE, CROSS JOIN, циклах или чем-то еще, на что фантазии хватит, но таблица чисел - самый лучший вариант. Делается один раз и надолго.
15 июл 11, 08:57    [10977684]     Ответить | Цитировать Сообщить модератору
 Re: Парсинг строки  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
ther
драсте...хотел бы поднять тему и спросить насчет запроса https://www.sql.ru/forum/actualutils.aspx?action=gotomsg&tid=843565&msg=10512891 от товарища iap

в таблице master.dbo.spt_values с type='P' количество в поле number всегда одинаково?
ибо опасно использовать этот запрос если поле будет допустим длинной 3000...но к моей задаче он подходит и меня поле журнала только 1000 символов....просто хотелось бы уточнить что количество number будет железно больше 1000...а пока я переписал сей запрос на использование временной таблицы с заполнением в цикле
Во-первых, можно посмотреть на таблицу SELECTом, и вопрос о количестве отпадёт.
Во-вторых, если версия >=2005, то можно с помощью ROW_NUMBER() пронумеровать в SELECTе любую таблицу, например с огромным количеством строк.
В-третьих, для версии >=2005 любое количество последовательных номеров можно получить рекурсивным CTE
В-четвёртых, любое количество последовательных чисел можно получить из нескольких CROSS JOIN небольших таблиц.
В-пятых, - САМОЕ ЛУЧШЕЕ - создать у себя постоянную таблицу с последовательными числами (хоть до миллиона!), проиндексировать её и использовать там, где на форуме написано master.dbo.spt_values WHERE type='P'

master.dbo.spt_values написано для примера! Просто она у всех заведомо есть.
15 июл 11, 09:03    [10977698]     Ответить | Цитировать Сообщить модератору
 Re: Парсинг строки  [new]
ther
Member

Откуда:
Сообщений: 839
ааа..понял пасиб
15 июл 11, 09:49    [10977850]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить