Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl→      все
 Replace substring c помощью Regex шаблона для MSSQL  [new]
alexxUK
Member

Откуда: Украина
Сообщений: 81
 		Доброго времени суток.
Возник следующий вопрос - пока навскидку приходит понятное мне решение через CLR функции, но может кто подскажет более эффективное решение, если есть таковое.

Проблема:
Есть ответы от клиента по опросникам - и в некоторых ответах горе-программисты втулили привязку к вопросам - то есть можно встретить строку ответка формата: 071-R01-No,071-R02-No,071-R03-03,071-R04-01

В результате ответы одного человека могут выглядеть вот так:
Yes
2
No
027-R01-Yes,027-R02-Yes,027-R03-01,027-R04-04
028-R01-Yes,028-R02-No,028-R03-02,028-R04-03

Из вот это радости нужно собрать финальную строку ответов - заменив все вхождения \d{3}-R\d{2}- на пустую строку.

то есть в простом случае: 071-R01-No,071-R02-No,071-R03-03,071-R04-01 должно стать No,No,03,01
Нашел тут ссылки на вроде похожие темы -
https://www.sql.ru/forum/571803/replace-s-opciey-match-whole-word?hl=sql regex replace
https://www.sql.ru/forum/826622/regexreplace?hl=sql regex replace

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

Количество ответов от людей - пока порядка 50 000 уникальных пользователей - рост будет не быстрый - может до 10 000 в месяц. База данных = MSSQL 2008 R2
Да, код приложения поменять уже поздно - клиент не разрешает категорически. (хотя было бы разумнее накатить скрипт по удалению существующий вхождений и обновить сайт с фиксом).

Заранее спасибо за ответы :)
16 май 13, 17:01    [14306984]     Ответить | Цитировать Сообщить модератору
 Re: Replace substring c помощью Regex шаблона для MSSQL  [new]
Glory
Member

Откуда:
Сообщений: 104751
alexxUK
Заранее спасибо за ответы

А вопрос то будет ?
16 май 13, 17:02    [14306992]     Ответить | Цитировать Сообщить модератору
 Re: Replace substring c помощью Regex шаблона для MSSQL  [new]
alexxUK
Member

Откуда: Украина
Сообщений: 81
 		Извините, вопрос

1) Как собрать финальную строку ответов - заменив все вхождения \d{3}-R\d{2}- на пустую строку?
2) Остановиться ли на CLR функции для базы или это омжно написать только нативными средствами SQL Server 2008?

то есть в простом случае: 071-R01-No,071-R02-No,071-R03-03,071-R04-01 должно стать No,No,03,01
16 май 13, 17:09    [14307043]     Ответить | Цитировать Сообщить модератору
 Re: Replace substring c помощью Regex шаблона для MSSQL  [new]
iap
Member

Откуда: Москва
Сообщений: 47198
 		alexxUK,

чем же отличаются ответы одного человека от ответов другого?
Напишите здесь CREATE TABLE и заполните примерными данными хотя бы для пары человек.
И PRINT @@VERSION опубликуйте
16 май 13, 17:09    [14307044]     Ответить | Цитировать Сообщить модератору
 Re: Replace substring c помощью Regex шаблона для MSSQL  [new]
alexxUK
Member

Откуда: Украина
Сообщений: 81
 		сейчас :), в процессе
16 май 13, 17:10    [14307050]     Ответить | Цитировать Сообщить модератору
 Re: Replace substring c помощью Regex шаблона для MSSQL  [new]
Гость333
Member

Откуда:
Сообщений: 3683
alexxUK
то есть в простом случае: 071-R01-No,071-R02-No,071-R03-03,071-R04-01 должно стать No,No,03,01

Вот решение на TSQL для простого случая:
declare @s varchar(100) = '071-R01-No,071-R02-No,071-R03-03,071-R04-01';
declare @i int = 1;
declare @length int = 8;

while @i > 0
begin
   set @i = patindex('%[0-9][0-9][0-9]-R[0-9][0-9]-%', @s);
   if @i > 0
      set @s = stuff(@s, @i, @length, '');
end;

print @s;
16 май 13, 17:11    [14307059]     Ответить | Цитировать Сообщить модератору
 Re: Replace substring c помощью Regex шаблона для MSSQL  [new]
Glory
Member

Откуда:
Сообщений: 104751
alexxUK
1) Как собрать финальную строку ответов - заменив все вхождения \d{3}-R\d{2}- на пустую строку?

нужено распарсить строки на записи

027-R01-Yes,027-R02-Yes,027-R03-01,027-R04-04
на
027-R01-Yes
027-R02-Yes
027-R03-01
027-R04-04
А потом удалить все левее последнего -
16 май 13, 17:12    [14307064]     Ответить | Цитировать Сообщить модератору
 Re: Replace substring c помощью Regex шаблона для MSSQL  [new]
alexxUK
Member

Откуда: Украина
Сообщений: 81
 		вот такие данные

База:
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[SurveyResponseByPage](
	[SysParticipantID] [bigint] NOT NULL,
	[SurveyID] [varchar](25) NOT NULL,
	[PageNumber] [int] NOT NULL,
	[Response] [varchar](2000) NULL	
 CONSTRAINT [PK_SurveyResponseByPage] PRIMARY KEY CLUSTERED 
(
	[SysParticipantID] ASC,
	[SurveyID] ASC,
	[PageNumber] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


Пример ужаса данных:

Insert into SurveyResponseByPage (SysParticipantID, SurveyID, PageNumber, Response)
Values(1,	'Test1',	1,	'001-Test Man1 ,002-Manager,003-Test Services,004-Alabama,005-IN,006-USA,007-12,008-Yes,009-8')

Insert into SurveyResponseByPage (SysParticipantID, SurveyID, PageNumber, Response)
Values(1,	'Test1',	2,	'010-Male,011-1963,012-White')

Insert into SurveyResponseByPage (SysParticipantID, SurveyID, PageNumber, Response)
Values(1,	'Test1',	3,	'013-test service,014-12,015-Yes,016-Do not know,017-Test Care,018-Test Facility,019-23,020-Yes,021-8,022-Yes,023-8,024-Yes,025-8,026-Yes')

Insert into SurveyResponseByPage (SysParticipantID, SurveyID, PageNumber, Response)
Values(1,	'Test1',	4,	'027-R01-Yes,027-R02-Yes,027-R03-04,027-R04-05,028-R01-Yes,028-R02-Yes,028-R03-04,028-R04-04,029-R01-Yes,029-R02-Yes,029-R03-04,029-R04-05,030-R01-Yes,030-R02-Yes,030-R03-04,030-R04-05,031-R01-Yes,031-R02-Yes,031-R03-04,031-R04-04,032-R01-Yes,032-R02-Yes,032-R03-04,032-R04-05,033-R01-No,033-R02-No,033-R03-03,033-R04-03,034-R01-Yes,034-R02-Yes,034-R03-05,034-R04-05,035-R01-Yes,035-R02-Yes,035-R03-05,035-R04-05,036-R01-Yes,036-R02-Yes,036-R03-04,036-R04-05,037-R01-Yes,037-R02-Yes,037-R03-05,037-R04-05')

Insert into SurveyResponseByPage (SysParticipantID, SurveyID, PageNumber, Response)
Values(1,	'Test1',	5,	'038-R01-Yes,038-R02-Yes,038-R03-05,038-R04-05,039-R01-Yes,039-R02-Yes,039-R03-05,039-R04-05,040-R01-Yes,040-R02-Yes,040-R03-05,040-R04-05,041-R01-Yes,041-R02-Yes,041-R03-05,041-R04-05,042-R01-Yes,042-R02-Yes,042-R03-04,042-R04-05,043-R01-Yes,043-R02-Yes,043-R03-05,043-R04-05,044-R01-Yes,044-R02-Yes,044-R03-05,044-R04-05,045-R01-Yes,045-R02-Yes,045-R03-04,045-R04-04,046-R01-No,046-R02-Yes,046-R03-04,046-R04-04,047-R01-Yes,047-R02-Yes,047-R03-05,047-R04-05,048-R01-No,048-R02-Yes,048-R03-04,048-R04-03,049-R01-Yes,049-R02-Yes,049-R03-05,049-R04-05,050-R01-Yes,050-R02-Yes,050-R03-05,050-R04-05')

Insert into SurveyResponseByPage (SysParticipantID, SurveyID, PageNumber, Response)
Values(1,	'Test1',	6,	'051-R01-No,051-R02-No,051-R03-04,051-R04-04,052-R01-Yes,052-R02-Yes,052-R03-05,052-R04-05,053-R01-Yes,053-R02-Yes,053-R03-05,053-R04-05,054-R01-Yes,054-R02-Yes,054-R03-05,054-R04-05,055-R01-Yes,055-R02-Yes,055-R03-05,055-R04-05,056-R01-Yes,056-R02-Yes,056-R03-05,056-R04-05,057-R01-Yes,057-R02-Yes,057-R03-05,057-R04-05,058-R01-Yes,058-R02-Yes,058-R03-05,058-R04-05,059-R01-Yes,059-R02-Yes,059-R03-05,059-R04-05,060-R01-Yes,060-R02-Yes,060-R03-05,060-R04-05,061-R01-Yes,061-R02-Yes,061-R03-05,061-R04-05,062-R01-Yes,062-R02-Yes,062-R03-05,062-R04-05,063-R01-Yes,063-R02-Yes,063-R03-05,063-R04-05')

Insert into SurveyResponseByPage (SysParticipantID, SurveyID, PageNumber, Response)
Values(1,	'Test1',	7,	'064-R01-Yes,064-R02-Yes,064-R03-04,064-R04-04,065-R01-Yes,065-R02-Yes,065-R03-05,065-R04-05,066-R01-Yes,066-R02-Yes,066-R03-05,066-R04-05,067-R01-Yes,067-R02-Yes,067-R03-05,067-R04-05,068-R01-Yes,068-R02-Yes,068-R03-05,068-R04-05,069-R01-Yes,069-R02-Yes,069-R03-05,069-R04-05,070-R01-Yes,070-R02-Yes,070-R03-05,070-R04-05,071-R01-Yes,071-R02-Yes,071-R03-05,071-R04-05,072-R01-Yes,072-R02-Yes,072-R03-05,072-R04-05,073-R01-Yes,073-R02-Yes,073-R03-05,073-R04-05,074-R01-No,074-R02-No,074-R03-03,074-R04-03,075-R01-Yes,075-R02-Yes,075-R03-04,075-R04-03,076-R01-Yes,076-R02-Yes,076-R03-05,076-R04-05')

Insert into SurveyResponseByPage (SysParticipantID, SurveyID, PageNumber, Response)
Values(1,	'Test1',	8,	'077-01-Yes,077-02-No,077-03-No,077-04-Yes,077-05-No,077-06-Yes,077-07-No,077-08-No,077-09-Yes,077-10-No,077-11-No,077-12-No,078-01-Yes,078-02-No,078-03-No,078-04-No,078-05-No,078-06-No,078-07-No,078-08-No,078-09-No,078-10-No,078-11-No,078-12-No,079-01-Yes,079-02-No,079-03-No,079-04-No,079-05-No,079-06-No,079-07-No,079-08-No,079-09-No,079-10-No,079-11-No,079-12-No,080-01-Yes,080-02-No,080-03-No,080-04-No,080-05-No,080-06-No,080-07-No,080-08-No,080-09-No,080-10-No,080-11-No,080-12-No,081-01-Yes,081-02-No,081-03-No,081-04-No,081-05-No,081-06-No,081-07-No,081-08-No,081-09-No,081-10-No,081-11-No,081-12-No,082-01-Yes,082-02-No,082-03-No,082-04-No,082-05-No,082-06-No,082-07-No,082-08-No,082-09-No,082-10-No,082-11-No,082-12-No,083-01-Yes,083-02-No,083-03-No,083-04-No,083-05-No,083-06-No,083-07-No,083-08-No,083-09-No,083-10-No,083-11-No,083-12-No,084-01-Yes,084-02-No,084-03-No,084-04-No,084-05-No,084-06-No,084-07-No,084-08-No,084-09-No,084-10-No,084-11-No,084-12-No,085-01-Yes,085-02-No,085-03-No,085-04-No,085-05-No,085-06-No,085-07-No,085-08-No,085-09-No,085-10-No,085-11-No,085-12-No,086-01-Yes,086-02-No,086-03-No,086-04-No,086-05-No,086-06-No,086-07-No,086-08-No,086-09-No,086-10-No,086-11-No,086-12-No,087-01-Yes,087-02-No,087-03-No,087-04-No,087-05-No,087-06-No,087-07-No,087-08-No,087-09-No,087-10-No,087-11-No,087-12-No')

Insert into SurveyResponseByPage (SysParticipantID, SurveyID, PageNumber, Response)
Values(1,	'Test1',	9,	'088-01-Yes,088-02-No,088-03-No,088-04-No,088-05-No,088-06-No,088-07-No,088-08-No,088-09-No,088-10-No,088-11-No,088-12-No,089-01-Yes,089-02-No,089-03-No,089-04-No,089-05-No,089-06-No,089-07-No,089-08-No,089-09-No,089-10-No,089-11-No,089-12-No,090-01-Yes,090-02-No,090-03-No,090-04-No,090-05-No,090-06-No,090-07-No,090-08-No,090-09-No,090-10-No,090-11-No,090-12-No,091-01-Yes,091-02-No,091-03-No,091-04-No,091-05-No,091-06-No,091-07-No,091-08-No,091-09-No,091-10-No,091-11-No,091-12-No,092-01-Yes,092-02-No,092-03-No,092-04-No,092-05-No,092-06-No,092-07-No,092-08-No,092-09-No,092-10-No,092-11-No,092-12-No,093-01-Yes,093-02-No,093-03-No,093-04-No,093-05-No,093-06-No,093-07-No,093-08-No,093-09-No,093-10-No,093-11-No,093-12-No,094-01-Yes,094-02-No,094-03-No,094-04-No,094-05-No,094-06-No,094-07-No,094-08-No,094-09-No,094-10-No,094-11-No,094-12-No,095-01-Yes,095-02-No,095-03-No,095-04-No,095-05-No,095-06-No,095-07-No,095-08-No,095-09-No,095-10-No,095-11-No,095-12-No,096-01-Yes,096-02-No,096-03-No,096-04-No,096-05-No,096-06-No,096-07-No,096-08-No,096-09-No,096-10-No,096-11-No,096-12-No,097-01-Yes,097-02-No,097-03-No,097-04-No,097-05-No,097-06-No,097-07-No,097-08-No,097-09-No,097-10-No,097-11-No,097-12-No,098-01-Yes,098-02-No,098-03-No,098-04-No,098-05-No,098-06-No,098-07-No,098-08-No,098-09-No,098-10-No,098-11-No,098-12-No,099-01-Yes,099-02-No,099-03-No,099-04-No,099-05-No,099-06-No,099-07-No,099-08-No,099-09-No,099-10-No,099-11-No,099-12-No')

Insert into SurveyResponseByPage (SysParticipantID, SurveyID, PageNumber, Response)
Values(1,	'Test1',	10,	'100-01-Yes,100-02-No,100-03-No,100-04-No,100-05-No,100-06-No,100-07-No,100-08-No,100-09-No,100-10-No,100-11-No,100-12-No,101-01-Yes,101-02-No,101-03-No,101-04-No,101-05-No,101-06-No,101-07-No,101-08-No,101-09-No,101-10-No,101-11-No,101-12-No,102-01-Yes,102-02-No,102-03-No,102-04-No,102-05-No,102-06-No,102-07-No,102-08-No,102-09-No,102-10-No,102-11-No,102-12-No,103-01-Yes,103-02-No,103-03-No,103-04-No,103-05-No,103-06-No,103-07-No,103-08-No,103-09-No,103-10-No,103-11-No,103-12-No,104-01-Yes,104-02-No,104-03-No,104-04-No,104-05-No,104-06-No,104-07-No,104-08-No,104-09-No,104-10-No,104-11-No,104-12-No,105-01-Yes,105-02-No,105-03-No,105-04-No,105-05-No,105-06-No,105-07-No,105-08-No,105-09-No,105-10-No,105-11-No,105-12-No,106-01-Yes,106-02-No,106-03-No,106-04-No,106-05-No,106-06-No,106-07-No,106-08-No,106-09-No,106-10-No,106-11-No,106-12-No,107-01-Yes,107-02-No,107-03-No,107-04-No,107-05-No,107-06-No,107-07-No,107-08-No,107-09-No,107-10-No,107-11-No,107-12-No,108-01-Yes,108-02-No,108-03-No,108-04-No,108-05-No,108-06-No,108-07-No,108-08-No,108-09-No,108-10-No,108-11-No,108-12-No,109-01-Yes,109-02-No,109-03-No,109-04-No,109-05-No,109-06-No,109-07-No,109-08-No,109-09-No,109-10-No,109-11-No,109-12-No,110-01-Yes,110-02-No,110-03-No,110-04-No,110-05-No,110-06-No,110-07-No,110-08-No,110-09-No,110-10-No,110-11-No,110-12-No,111-01-Yes,111-02-No,111-03-No,111-04-No,111-05-No,111-06-No,111-07-No,111-08-No,111-09-No,111-10-No,111-11-No,111-12-No')

Insert into SurveyResponseByPage (SysParticipantID, SurveyID, PageNumber, Response)
Values(1,	'Test1',	11,	'112-01-Yes,112-02-No,112-03-No,112-04-No,112-05-No,112-06-No,112-07-No,112-08-No,112-09-No,112-10-No,112-11-No,112-12-No,113-01-Yes,113-02-No,113-03-No,113-04-No,113-05-No,113-06-No,113-07-No,113-08-No,113-09-No,113-10-No,113-11-No,113-12-No,114-01-Yes,114-02-No,114-03-No,114-04-No,114-05-No,114-06-No,114-07-No,114-08-No,114-09-No,114-10-No,114-11-No,114-12-No,115-01-Yes,115-02-No,115-03-No,115-04-No,115-05-No,115-06-No,115-07-No,115-08-No,115-09-No,115-10-No,115-11-No,115-12-No,116-01-Yes,116-02-No,116-03-No,116-04-No,116-05-No,116-06-No,116-07-No,116-08-No,116-09-No,116-10-No,116-11-No,116-12-No,117-01-Yes,117-02-No,117-03-No,117-04-No,117-05-No,117-06-No,117-07-No,117-08-No,117-09-No,117-10-No,117-11-No,117-12-No,118-01-Yes,118-02-No,118-03-No,118-04-No,118-05-No,118-06-No,118-07-No,118-08-No,118-09-No,118-10-No,118-11-No,118-12-No,119-01-Yes,119-02-No,119-03-No,119-04-No,119-05-No,119-06-No,119-07-No,119-08-No,119-09-No,119-10-No,119-11-No,119-12-No,120-01-Yes,120-02-No,120-03-No,120-04-No,120-05-No,120-06-No,120-07-No,120-08-No,120-09-No,120-10-No,120-11-No,120-12-No,121-01-Yes,121-02-No,121-03-No,121-04-No,121-05-No,121-06-No,121-07-No,121-08-No,121-09-No,121-10-No,121-11-No,121-12-No,122-01-Yes,122-02-No,122-03-No,122-04-No,122-05-No,122-06-No,122-07-No,122-08-No,122-09-No,122-10-No,122-11-No,122-12-No,123-01-Yes,123-02-No,123-03-No,123-04-No,123-05-No,123-06-No,123-07-No,123-08-No,123-09-No,123-10-No,123-11-No,123-12-No')
16 май 13, 17:27    [14307167]     Ответить | Цитировать Сообщить модератору
 Re: Replace substring c помощью Regex шаблона для MSSQL  [new]
alexxUK
Member

Откуда: Украина
Сообщений: 81
 		версия - до клиента достучаться не могу, но вроде вот такое будет

Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64)
Sep 16 2010 19:43:16
Copyright (c) 1988-2008 Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) (VM)
16 май 13, 17:28    [14307178]     Ответить | Цитировать Сообщить модератору
 Re: Replace substring c помощью Regex шаблона для MSSQL  [new]
alexxUK
Member

Откуда: Украина
Сообщений: 81
Гость333
alexxUK
то есть в простом случае: 071-R01-No,071-R02-No,071-R03-03,071-R04-01 должно стать No,No,03,01

Вот решение на TSQL для простого случая:
declare @s varchar(100) = '071-R01-No,071-R02-No,071-R03-03,071-R04-01';
declare @i int = 1;
declare @length int = 8;

while @i > 0
begin
   set @i = patindex('%[0-9][0-9][0-9]-R[0-9][0-9]-%', @s);
   if @i > 0
      set @s = stuff(@s, @i, @length, '');
end;

print @s;


идея интересная - это для случая не аггрегированных данных можно попробовать (промежуточная таблица), спасибо

Glory
alexxUK
1) Как собрать финальную строку ответов - заменив все вхождения \d{3}-R\d{2}- на пустую строку?

нужено распарсить строки на записи

027-R01-Yes,027-R02-Yes,027-R03-01,027-R04-04
на
027-R01-Yes
027-R02-Yes
027-R03-01
027-R04-04
А потом удалить все левее последнего -


была мысль положиться на дефис, но изначально не все описал, как вижу сейчас - возможно ответы клиента с дефисами в самом ответе. И да - некоторые содержат просто \d{3}- паттерн
16 май 13, 17:34    [14307216]     Ответить | Цитировать Сообщить модератору
 Re: Replace substring c помощью Regex шаблона для MSSQL  [new]
iap
Member

Откуда: Москва
Сообщений: 47198
 		alexxUK,

всё-таки, надо формализовать.
А то как же?
16 май 13, 17:37    [14307238]     Ответить | Цитировать Сообщить модератору
 Re: Replace substring c помощью Regex шаблона для MSSQL  [new]
alexxUK
Member

Откуда: Украина
Сообщений: 81
 		не аггрегированные данные - тоже есть в базе
Таблица 
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[SurveyAnswers](
	[SysParticipantID] [bigint] NOT NULL,
	[SurveyID] [varchar](25) NOT NULL,
	[QuestionNumber] [int] NOT NULL,
	[PageNumber] [int] NULL,
	[QuestionResponse] [varchar](1000) NULL	
 CONSTRAINT [PK_SurveyAnswers] PRIMARY KEY CLUSTERED 
(
	[SysParticipantID] ASC,
	[SurveyID] ASC,
	[QuestionNumber] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


Данные - выборки кусок:
Insert into surveyAnswers (SysParticipantID,	SurveyID,	QuestionNumber,	PageNumber,	QuestionResponse)
Values( 1,	'Test1',	16	, 3, 	'Do not know')

Insert into surveyAnswers (SysParticipantID,	SurveyID,	QuestionNumber,	PageNumber,	QuestionResponse)
Values(1,	'Test1',	17	, 3, 	'Tets Care')

Insert into surveyAnswers (SysParticipantID,	SurveyID,	QuestionNumber,	PageNumber,	QuestionResponse)
Values(1,	'Test1',	18	, 3, 	'Test Facility')

Insert into surveyAnswers (SysParticipantID,	SurveyID,	QuestionNumber,	PageNumber,	QuestionResponse)
Values(1,	'Test1',	19	, 3, 	'23')

Insert into surveyAnswers (SysParticipantID,	SurveyID,	QuestionNumber,	PageNumber,	QuestionResponse)
Values(1,	'Test1',	20	, 3, 	'Yes')

Insert into surveyAnswers (SysParticipantID,	SurveyID,	QuestionNumber,	PageNumber,	QuestionResponse)
Values(1,	'Test1',	21	, 3, 	'8')

Insert into surveyAnswers (SysParticipantID,	SurveyID,	QuestionNumber,	PageNumber,	QuestionResponse)
Values(1,	'Test1',	22	, 3, 	'Yes')

Insert into surveyAnswers (SysParticipantID,	SurveyID,	QuestionNumber,	PageNumber,	QuestionResponse)
Values(1,	'Test1',	23	, 3, 	'8')

Insert into surveyAnswers (SysParticipantID,	SurveyID,	QuestionNumber,	PageNumber,	QuestionResponse)
Values(1,	'Test1',	24	, 3, 	'Yes')

Insert into surveyAnswers (SysParticipantID,	SurveyID,	QuestionNumber,	PageNumber,	QuestionResponse)
Values(1,	'Test1',	25	, 3, 	'8')

Insert into surveyAnswers (SysParticipantID,	SurveyID,	QuestionNumber,	PageNumber,	QuestionResponse)
Values(1,	'Test1',	26	, 3, 	'Yes')

Insert into surveyAnswers (SysParticipantID,	SurveyID,	QuestionNumber,	PageNumber,	QuestionResponse)
Values(1,	'Test1',	27	, 4, 	'027-R01-Yes,027-R02-Yes,027-R03-04,027-R04-05')

Insert into surveyAnswers (SysParticipantID,	SurveyID,	QuestionNumber,	PageNumber,	QuestionResponse)
Values(1,	'Test1',	28	, 4, 	'028-R01-Yes,028-R02-Yes,028-R03-04,028-R04-04')

Insert into surveyAnswers (SysParticipantID,	SurveyID,	QuestionNumber,	PageNumber,	QuestionResponse)
Values(1,	'Test1',	29	, 4, 	'029-R01-Yes,029-R02-Yes,029-R03-04,029-R04-05')

Insert into surveyAnswers (SysParticipantID,	SurveyID,	QuestionNumber,	PageNumber,	QuestionResponse)
Values(1,	'Test1',	30	, 4, 	'030-R01-Yes,030-R02-Yes,030-R03-04,030-R04-05')

Insert into surveyAnswers (SysParticipantID,	SurveyID,	QuestionNumber,	PageNumber,	QuestionResponse)
Values(1,	'Test1',	31	, 4, 	'031-R01-Yes,031-R02-Yes,031-R03-04,031-R04-04')
16 май 13, 17:47    [14307305]     Ответить | Цитировать Сообщить модератору
 Re: Replace substring c помощью Regex шаблона для MSSQL  [new]
alexxUK
Member

Откуда: Украина
Сообщений: 81
 		пока у меня есть стойкое желание сделать на С# = там мне просто понятнее, но если это затронет производительность = то тогда хочу сделать в базе ).
16 май 13, 17:48    [14307319]     Ответить | Цитировать Сообщить модератору
 Re: Replace substring c помощью Regex шаблона для MSSQL  [new]
Гость333
Member

Откуда:
Сообщений: 3683
alexxUK
желание сделать на С#

Очень правильное желание.

alexxUK
но если это затронет производительность

Какую именно производительность вы имеете в виду?
16 май 13, 17:52    [14307343]     Ответить | Цитировать Сообщить модератору
 Re: Replace substring c помощью Regex шаблона для MSSQL  [new]
alexxUK
Member

Откуда: Украина
Сообщений: 81
 		Гость333,

сейчас - сырой запрос (с неверными данными) по всем товарищам выдается порядка за 30 секунд - код кто-то тут наваял.
Под производительностью - имеется ввиду - чтобы было время соизмеримое с текущей выборкой, учитывая что вообще regex будет немного тяжеловат (как мне кажется).

Надо сделать и запустить несколько параллельных запросов - посмотреть, что скажет сервер на это (по ресурсам) :)
16 май 13, 17:56    [14307379]     Ответить | Цитировать Сообщить модератору
 Re: Replace substring c помощью Regex шаблона для MSSQL  [new]
alexxUK
Member

Откуда: Украина
Сообщений: 81
 		итого - что навскидку получилось.

1) Сделал вот такую хрень
private static readonly Regex longRegex = new Regex(@"\d{3}-R\d{2}-");
private static readonly Regex shortRegex = new Regex(@"\d{3}-");

    /// <summary>
    /// function to perform text transformations for SQL Data
    /// </summary>
    /// <param name="reportData">actual string to modify</param>
    /// <param name="isToVerifyMarch">flag to identify if we need to perform validation for template occurence between transformation</param>
    /// <param name="isShortPattern">option to identify pattern that we need to use for transformations</param>
    /// <returns></returns>
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString RegexReplaceParticipantResponse(SqlString reportData, SqlBoolean isToVerifyMatch, SqlBoolean isShortPattern)
    {
        // return compiled string with performed replacements
        if (isShortPattern)
        {
            if (!isToVerifyMatch || shortRegex.IsMatch(reportData.Value))
            {
                return new SqlString(shortRegex.Replace(reportData.Value, string.Empty));
            }
        }
        else
        {
            if (!isToVerifyMatch || longRegex.IsMatch(reportData.Value))
            {
                return new SqlString(longRegex.Replace(reportData.Value, string.Empty));
            }
        }
        // no match has been found so far
        return reportData; 
    }


2: разрешил серверу работать с такими функциями:
-- 1 : reconfigure the DB to allow CLR 
sp_configure 'clr enabled', 1
RECONFIGURE WITH OVERRIDE



3: зарегистрировал ее на сервере (для базы): 
-- 2 : register assembly within MSSQL
use [_YourDB_]
CREATE ASSEMBLY RegX FROM '_YourPath\_YourDll_.dll'


4: cделал функцию в базе
CREATE Function RegExReplace
(
  @reportData NVARCHAR(2000),
  @isToVerifyMatch BIT,
  @isShortPattern BIT  
)
RETURNS NVARCHAR(2000) EXTERNAL NAME RegX.UserDefinedFunctions.RegexReplaceParticipantResponse


5: Тестируем

-- Testing 
Declare @sampleResultLong NVARCHAR(2000)
Declare @sampleResultLongMod NVARCHAR(2000)
Set @sampleResultLong = '027-R01-Yes,027-R02-Yes,027-R03-04,027-R04-05,028-R01-Yes,028-R02-Yes,028-R03-04,028-R04-04,029-R01-Yes,029-R02-Yes,029-R03-04,029-R04-05,030-R01-Yes,030-R02-Yes,030-R03-04,030-R04-05,031-R01-Yes,031-R02-Yes,031-R03-04,031-R04-04,032-R01-Yes,032-R02-Yes,032-R03-04,032-R04-05,033-R01-No,033-R02-No,033-R03-03,033-R04-03,034-R01-Yes,034-R02-Yes,034-R03-05,034-R04-05,035-R01-Yes,035-R02-Yes,035-R03-05,035-R04-05,036-R01-Yes,036-R02-Yes,036-R03-04,036-R04-05,037-R01-Yes,037-R02-Yes,037-R03-05,037-R04-05'
Select @sampleResultLongMod = dbo.RegExReplace(@sampleResultLong, 'TRUE', 'FALSE')
Print @sampleResultLongMod

Declare @sampleResultShort NVARCHAR(2000)
Declare @sampleResultShortMod NVARCHAR(2000)
Set @sampleResultShort = '013-collections,014-less than 1 year,015-Yes,016-Do not know,017-Test - Collections,018-Test awesome Facility,019-3,020-Yes,021-3,022-No,023-,024-No,025-,026-Yes'
Select @sampleResultShortMod = dbo.RegExReplace(@sampleResultShort, 'TRUE', 'TRUE')
Print @sampleResultShortMod


осталось только придумать - как эффективнее это все вызывать
вывод одного результата для человека:
1) Таблица ResponseByPage : возможно есть смысл соединять строки 1-3 - так как они будут работать по облегченному паттерну - и менять их, а потом делать то же со строками 4-конец (в них вводится второй шаблон замены)- и эти 2 результата склеивать потом

2) Таблица Response = делать выборку по всем айтемам до определенного номера вопроса - там где нет вставок, а потом соединять вопросы со вставками = 027-R01-, обновлять вторую часть и потом это все дело соединять с первой частью?

Еще прикол, что это надо собрать для всех людей (критерием является surveyID) - а я в SQL мягко говоря, немного туповат ). Может кто пнет в нужном направлении - как это пооптимальнее собрать?

Заранее спасибо за идеи и комментарии
16 май 13, 19:55    [14307970]     Ответить | Цитировать Сообщить модератору
 Re: Replace substring c помощью Regex шаблона для MSSQL  [new]
alexxUK
Member

Откуда: Украина
Сообщений: 81
 		ожидаемый результат для одного человека, как пример - 
-- First part - easy one (use formalized table)
DECLARE @FirstResults NVARCHAR(1000) 
SELECT @FirstResults = COALESCE(@FirstResults + ',', '') + ISNULL(QuestionResponse, '')
FROM [MIGSurvey].[dbo].[ParticipantSurveyAnswers]
Where SurveyID = 'Test1'
AND SysParticipantID = 1
And PageNumber < 4
Order by QuestionNumber
Print  @FirstResults

-- second part - use generic table to reduce amount of strings to work with
DECLARE @SecondResults NVARCHAR(4000) 
SELECT @SecondResults = COALESCE(@SecondResults + ',', '') + ISNULL(QuestionResponse, '')
FROM [MIGSurvey].[dbo].[ParticipantSurveyAnswers]
Where SurveyID = 'Test1'
AND SysParticipantID = 1
And PageNumber >= 4
Order by QuestionNumber
SET @SecondResults = dbo.RegExReplace(@SecondResults, 'TRUE', 'FALSE')
Print @SecondResults

-- third part: final result for one person
Declare @FinalResults NVARCHAR(max)
SET @FinalResults = @firstResults + ',' + @SecondResults + ','
Print @FinalResults
16 май 13, 20:22    [14308084]     Ответить | Цитировать Сообщить модератору
 Re: Replace substring c помощью Regex шаблона для MSSQL  [new]
Glory
Member

Откуда:
Сообщений: 104751
alexxUK
осталось только придумать - как эффективнее это все вызывать
вывод одного результата для человека:
1) Таблица ResponseByPage : возможно есть смысл соединять строки 1-3 - так как они будут работать по облегченному паттерну - и менять их, а потом делать то же со строками 4-конец (в них вводится второй шаблон замены)- и эти 2 результата склеивать потом

2) Таблица Response = делать выборку по всем айтемам до определенного номера вопроса - там где нет вставок, а потом соединять вопросы со вставками = 027-R01-, обновлять вторую часть и потом это все дело соединять с первой частью?

Еще прикол, что это надо собрать для всех людей (критерием является surveyID) - а я в SQL мягко говоря, немного туповат ). Может кто пнет в нужном направлении - как это пооптимальнее собрать?

Скажите, вот вы серьезно думаете хранить реляционные данные в одном символьном поле ? И при каждом запросе преобразовывать это поле к нужному виду ?
17 май 13, 09:08    [14309343]     Ответить | Цитировать Сообщить модератору
 Re: Replace substring c помощью Regex шаблона для MSSQL  [new]
alexxUK
Member

Откуда: Украина
Сообщений: 81
 		Glory,

:), повторюсь, решение уже досталось от предыдущих товарищей, оно как-то работает и клиент категорически отказывается что-то менять в существующем коде, так что "маемо шо маемо" - теперь надо минимизировать извините за выражение "*опу" и сделать как-то замену для отчетов.

Замену строк реализовал через регекс - написано выше, постарался собрать отчет по этому горю - в итоге время выполнения порядка 60 сек - на 1100 уникальных строк отчета - что, конечно, более полная ...., чем думалось.

пример запроса по всем товарищам, что придумалось вечером с формализированной таблицей (в этом деле не сильно силен - надо учиться ) :). - далеается 2 замены регекспом.

Select distinct dbo.RegExReplaceCommon(substring(
	(Select ','+ST1.QuestionResponse  AS [text()]
        From [MIGSurvey].[dbo].[SurveyAnswers] ST1
        Where ST1.SysParticipantID = ST2.SysParticipantID 
          AND ST1.QuestionResponse != 'NULL' -- ? need to replace with empty string
        ORDER BY ST1.QuestionNumber
        For XML PATH ('')
    ),2, 3000), 'FALSE') [Response]
 From [MIGSurvey].[dbo].[SurveyAnswers] ST2
 Where ST2.SurveyID='Test1' 
    AND ST2.QuestionResponse != 'NULL' -- -- ? need to replace with empty string


Хотя вот здесь вполне логично делать только одну замену, так как первые данные идут без префиксов.

Вот думаю, если сослаться на вторую таблицу (SurveyResponseByPage) - где записаны уже меньшее количество строк и джоинов будет меньше - то может будет немного повеселее.

Если не получится чего-то хорошего, тогда возможно будем еще раз аргументированно обьяснять, и вводить другие обьекты в базу, чтобы это починить. :)
17 май 13, 10:39    [14309838]     Ответить | Цитировать Сообщить модератору
 Re: Replace substring c помощью Regex шаблона для MSSQL  [new]
Glory
Member

Откуда:
Сообщений: 104751
alexxUK
:), повторюсь, решение уже досталось от предыдущих товарищей

Какое решение то ? Парсить каждый раз поле ?

alexxUK
оно как-то работает и клиент категорически отказывается что-то менять в существующем коде

Код вы создаете сейчас. А я говорю про структуру.
17 май 13, 10:43    [14309864]     Ответить | Цитировать Сообщить модератору
 Re: Replace substring c помощью Regex шаблона для MSSQL  [new]
alexxUK
Member

Откуда: Украина
Сообщений: 81
 		Glory,

И да, по второй части ...
этот запрос будет вызываться возможно раз в день - поскольку там несколько параллельных и независимых типов - то несколько раз в сутки для всех. Хочется просто уже с минимальными потерями допилить это до чего то более или менее адекватного.

Подумалось сделать кстати еще одну таблицу (мысли вслух, так сказать), куда бы процедура складывала результаты выполнения преобразования - и потом при вызове этого функционала - только допихивало новые записи в финальную таблицу с преобразованием, а потом делать уже просто выборку из финальной таблицы (забыл добавить, данные по клиенту каждый раз выгребаются все - без привязки к дням или еще чему то... почему так - никто не обьясняет, типа работает же).
То есть в идеале - если новых записей нет, то мы просто всегда делаем уже select по готовым данным. Елинственное что - иногда данные могут быть обновлены клиентом позже - то есть надо будет еще удалять старые данные по ключу, клиента - если есть обновленная версия (дата модификации) - и возможно это стоит сделать отдельными таблицами - то есть каждый клиент будет иметь только свою таблицу вот таких суммарных ответов, и не будет наразберихи в момент вставки, обновлений, перестроений и прочего.

Изменения кода не будет - будет только новая процедура и таблицы ).
Насколько несерьезно это все звучит?
17 май 13, 10:48    [14309923]     Ответить | Цитировать Сообщить модератору
 Re: Replace substring c помощью Regex шаблона для MSSQL  [new]
alexxUK
Member

Откуда: Украина
Сообщений: 81
Glory
alexxUK
:), повторюсь, решение уже досталось от предыдущих товарищей

Какое решение то ? Парсить каждый раз поле ?

alexxUK
оно как-то работает и клиент категорически отказывается что-то менять в существующем коде

Код вы создаете сейчас. А я говорю про структуру.


код - что я создал - это отдельная функция, которая встраивается в фиквел и протсо делает регексп.
Клиент не хочет менять существующее решение по сбору и сохранению таких данных, возможно тут недоразумение было.

Но ему нужен отчет по этому - то есть добавлять что-то я могу, менять существующее - категорически нет.
17 май 13, 10:51    [14309943]     Ответить | Цитировать Сообщить модератору
 Re: Replace substring c помощью Regex шаблона для MSSQL  [new]
alexxUK
Member

Откуда: Украина
Сообщений: 81
Glory
Какое решение то ? Парсить каждый раз поле ?


решение сбора и сохранения данных по опросам.
17 май 13, 10:52    [14309955]     Ответить | Цитировать Сообщить модератору
 Re: Replace substring c помощью Regex шаблона для MSSQL  [new]
alexxUK
Member

Откуда: Украина
Сообщений: 81
 		alexxUK,

Если кому интересно - вот такой запрос выполнился за 7 сек, вернув 1200 записей

-- clear cache for testi purposes
CHECKPOINT; 
GO 
DBCC DROPCLEANBUFFERS; 
GO

select distinct dbo.RegExReplaceCommon(
	substring(
		(Select ',' + ST1.Response AS [text()]
			From [MIGSurvey].[dbo].SurveyResponseByPage ST1
			Where ST1.SysParticipantID = ST2.SysParticipantID         			
			  AND ST1.Response is not NULL
			ORDER BY ST1.PageNumber
			For XML PATH ('')
		),2, 3000
	), 'FALSE') [Response]
 From [MIGSurvey].[dbo].[SurveyResponseByPage] ST2
 Where ST2.SurveyID='Test1'     
    AND ST2.Response is not NULL


осталось только понять, почему выборка разная, получилась ), и как это можно еще соптимизировать
17 май 13, 11:33    [14310300]     Ответить | Цитировать Сообщить модератору
 Re: Replace substring c помощью Regex шаблона для MSSQL  [new]
alexxUK
Member

Откуда: Украина
Сообщений: 81
 		ну и само тело метода - которое проверяет на вхождение 2 регекспа подряд = может и коряво немного, но для старта полета мысли что-то есть

/// <summary>
    /// function to perform text transformations for SQL Data based on both validations
    /// </summary>
    /// <param name="reportData">actual string to modify</param>
    /// <param name="isToVerifyMarch">flag to identify if we need to perform validation for template occurence between transformation</param>
    /// <returns></returns>
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString RegexReplaceResponseCommon(SqlString reportData, SqlBoolean isToVerifyMatch)
    {
        // return compiled string with performed replacements
        if (!isToVerifyMatch || longRegex.IsMatch(reportData.Value))
        {
            var tempString = longRegex.Replace(reportData.Value, string.Empty);
            if (!isToVerifyMatch || shortRegex.IsMatch(tempString))
            {
                return new SqlString(shortRegex.Replace(tempString, string.Empty));
            }
        }
        else
        {   
            if (!isToVerifyMatch || shortRegex.IsMatch(reportData.Value))
            {
                return new SqlString(shortRegex.Replace(reportData.Value, string.Empty));
            }
        }
        // no matches has been found so far
        return reportData;
    }


всем отписавшимся большое спасибо за ответы и советы )
17 май 13, 11:37    [14310331]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl→      все
Все форумы / Microsoft SQL Server Ответить