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

Откуда:
Сообщений: 5
Здравствуйте.
Помогите, пожалуйста, с запросом. Есть две таблицы.
CREATE TABLE [#a1] (
[tab] [int] NULL ,
[kod] [int] NULL )

CREATE TABLE [#a2] (
[tab] [int] NULL ,
[skod] [varchar](50) NULL)

insert into #a1 (tab, kod) (select 10,1)
insert into #a1 (tab, kod) (select 10,10)
insert into #a1 (tab, kod) (select 12,1)
insert into #a1 (tab, kod) (select 12,3)

insert into #a2 (tab, k) (select 10,'1#3#10#')
insert into #a2 (tab, k) (select 12,'1#3#6#')


table #a1
tab kod
10 1
10 10
12 1
12 3

table #a2
tab skod
10 1#3#10#
12 1#3#6#


Нужно получить такие данные
tab skod
10 3#
12 6#


Т.е. по таб.№ нужно определить какой код из таблицы #a1 не входит в строку кодов таблицы #a2.
Получается удалить только по одному коду
select a.tab, b.kod, stuff(a.k,PATINDEX('%'+ltrim(str(b.kod))+'#%',a.k),len(ltrim(str(b.kod))+'#'),'') from #a2 a inner join #a1 b on a.tab=b.tab
21 авг 09, 11:22    [7565549]     Ответить | Цитировать Сообщить модератору
 Re: Удаление символов в строке  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
Коды в таблице #a2 всегда разделены строго одной решеткой?
21 авг 09, 11:27    [7565608]     Ответить | Цитировать Сообщить модератору
 Re: Удаление символов в строке  [new]
Anddros
Member

Откуда:
Сообщений: 1077
Для 2005/2008:
CREATE TABLE [#a1] (
[tab] [int] NULL ,
[kod] [int] NULL )

CREATE TABLE [#a2] (
[tab] [int] NULL ,
[k] [varchar](50) NULL)

insert into #a1 (tab, kod) (select 10,1)
insert into #a1 (tab, kod) (select 10,10)
insert into #a1 (tab, kod) (select 12,1)
insert into #a1 (tab, kod) (select 12,3)

insert into #a2 (tab, k) (select 10,'1#3#10#')
insert into #a2 (tab, k) (select 12,'1#3#6#')

;with q as (select tab t, kod, row_number()over(partition by tab order by (select 1)) rn from #a1),
q1 as (select tab, k, cast('#'+cast(k as varchar(10)) as varchar(8000)) k2, 1 r
from #a2
union all
select tab, k, cast(replace(k2,'#'+cast(kod as varchar(10))+'#','#') as varchar(8000)), r+1
from q1
inner join q on t=tab and r=rn
)

select top 1 with ties tab, substring(k2,2,8000) 
from q1
order by row_number()over(partition by tab order by len(k2))
option (maxrecursion 0)
21 авг 09, 11:47    [7565805]     Ответить | Цитировать Сообщить модератору
 Re: Удаление символов в строке  [new]
Zlatka
Member

Откуда:
Сообщений: 5
Zlatka
Здравствуйте.
Помогите, пожалуйста, с запросом. Есть две таблицы.
CREATE TABLE [#a1] (
[tab] [int] NULL ,
[kod] [int] NULL )

CREATE TABLE [#a2] (
[tab] [int] NULL ,
[skod] [varchar](50) NULL)

insert into #a1 (tab, kod) (select 10,1)
insert into #a1 (tab, kod) (select 10,10)
insert into #a1 (tab, kod) (select 12,1)
insert into #a1 (tab, kod) (select 12,3)

insert into #a2 (tab, k) (select 10,'1#3#10#')
insert into #a2 (tab, k) (select 12,'1#3#6#')


table #a1
tab kod
10 1
10 10
12 1
12 3

table #a2
tab skod
10 1#3#10#
12 1#3#6#


Нужно получить такие данные
tab skod
10 3#
12 6#


Т.е. по таб.№ нужно определить какой код из таблицы #a1 не входит в строку кодов таблицы #a2.
Получается удалить только по одному коду
select a.tab, b.kod, stuff(a.k,PATINDEX('%'+ltrim(str(b.kod))+'#%',a.k),len(ltrim(str(b.kod))+'#'),'') from #a2 a inner join #a1 b on a.tab=b.tab

да
21 авг 09, 12:07    [7565965]     Ответить | Цитировать Сообщить модератору
 Re: Удаление символов в строке  [new]
Zlatka
Member

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

спасибо, нужно для 2000
21 авг 09, 12:07    [7565972]     Ответить | Цитировать Сообщить модератору
 Re: Удаление символов в строке  [new]
Zlatka
Member

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

да
21 авг 09, 12:08    [7565976]     Ответить | Цитировать Сообщить модератору
 Re: Удаление символов в строке  [new]
Anddros
Member

Откуда:
Сообщений: 1077
Zlatka
Anddros,

спасибо, нужно для 2000

Для 2000-го - цикл или курсор. Например, так:
CREATE TABLE [#a1] (
[tab] [int] NULL ,
[kod] [int] NULL )

CREATE TABLE [#a2] (
[tab] [int] NULL ,
[k] [varchar](50) NULL)

insert into #a1 (tab, kod) (select 10,1)
insert into #a1 (tab, kod) (select 10,10)
insert into #a1 (tab, kod) (select 12,1)
insert into #a1 (tab, kod) (select 12,3)

insert into #a2 (tab, k) (select 10,'1#3#10#')
insert into #a2 (tab, k) (select 12,'1#3#6#')

declare @kod int
declare @tab int

select tab, '#'+k k2
into #a3
from #a2
create index idx_a3 on #a3 (tab)

declare cur cursor local for
select tab, kod from #a1
open cur
fetch next from cur into @tab,@kod
while @@fetch_status=0
begin
  update #a3 set
  k2=replace(k2,'#'+cast(@kod as varchar(10))+'#','#')
  where tab=@tab
  fetch next from cur into @tab,@kod
end
close cur
deallocate cur

select tab, substring(k2,2,8000) from #a3
21 авг 09, 13:33    [7566632]     Ответить | Цитировать Сообщить модератору
 Re: Удаление символов в строке  [new]
Zlatka
Member

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

спасибо, с курсором реализовано. С большими объемами долго выполняется. Нужно переходить на 2005. Всем, еще раз, спасибо за помощь
21 авг 09, 14:08    [7566864]     Ответить | Цитировать Сообщить модератору
 Re: Удаление символов в строке  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Anddros, зачем курсор, слюшай?!
SET NOCOUNT ON;
USE tempdb;

IF OBJECT_ID(N'tempdb..#a1',N'U') IS NOT NULL DROP TABLE [#a1];
IF OBJECT_ID(N'tempdb..#a2',N'U') IS NOT NULL DROP TABLE [#a2];

CREATE TABLE [#a1] (
[tab] [int] NULL ,
[kod] [int] NULL )

CREATE TABLE [#a2] (
[tab] [int] NULL ,
[skod] [varchar](50) NULL)

insert into #a1 (tab, kod) (select 10,1)
insert into #a1 (tab, kod) (select 10,10)
insert into #a1 (tab, kod) (select 12,1)
insert into #a1 (tab, kod) (select 12,3)

insert into #a2 (tab, [skod]) (select 10,'1#3#10#')
insert into #a2 (tab, [skod]) (select 12,'1#3#6#')

SELECT T.[tab], T.[skod]+'#' FROM
(
 SELECT T.[tab], SUBSTRING(T.[skod],V.number,
  (
   SELECT MIN(VV.number)
   FROM master.dbo.spt_values VV JOIN #a2 TT
   ON TT.[tab]=T.[tab] AND VV.type='P' AND VV.number BETWEEN V.number AND LEN(TT.[skod])+1
   AND SUBSTRING(TT.[skod]+'#',VV.number,1)='#'
  )-V.number
 )
 FROM #a2 T CROSS JOIN master.dbo.spt_values V
 WHERE V.type='P' AND V.number BETWEEN 1 AND LEN(T.[skod])+1
 AND SUBSTRING('#'+T.[skod],V.number,1)='#'
)T([tab],[skod])
LEFT JOIN  #a1 ON T.[tab]=#a1.[tab] AND CAST(T.[skod] AS INT)=#a1.kod
WHERE T.[skod]>'' AND #a1.[tab] IS NULL
ORDER BY T.[tab];
21 авг 09, 14:16    [7566920]     Ответить | Цитировать Сообщить модератору
 Re: Удаление символов в строке  [new]
ChA
Member

Откуда: Москва
Сообщений: 10985
До кучи
CREATE TABLE [#a1] (
[tab] [int] NULL ,
[kod] [int] NULL )

CREATE TABLE [#a2] (
[tab] [int] NULL ,
[skod] [varchar](50) NULL)

insert into #a1 (tab, kod) (select 10,1)
insert into #a1 (tab, kod) (select 10,10)
insert into #a1 (tab, kod) (select 12,1)
insert into #a1 (tab, kod) (select 12,3)

insert into #a2 (tab, [skod]) (select 10,'1#3#10#')
insert into #a2 (tab, [skod]) (select 12,'1#3#6#')

DECLARE @tab int, @skod varchar(50)
CREATE TABLE #t (tab int, kod varchar(12), skod varchar(50), PRIMARY KEY(tab, kod))
INSERT INTO #t 
SELECT a1.tab, CAST(a1.kod AS varchar(11))+'#', skod
FROM #a2 a2
INNER JOIN #a1 a1 ON (a1.tab = a2.tab AND CHARINDEX(CAST(a1.kod AS varchar(11))+'#', skod) > 0)
UPDATE t
SET @skod = skod = CASE @tab WHEN ISNULL(tab, @tab) THEN REPLACE(@skod, kod, '') ELSE REPLACE(skod, kod, '') END, @tab = tab
FROM #t t
SELECT t1.tab, t1.skod
FROM #t t1
WHERE t1.kod = (SELECT MAX(t2.kod) FROM #t t2 WHERE t2.tab = t1.tab)
GO
DROP TABLE #a2, #a1, #t
21 авг 09, 14:22    [7566971]     Ответить | Цитировать Сообщить модератору
 Re: Удаление символов в строке  [new]
iap
Member

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

кстати говоря, моё решение не обращает внимания на количество решёток подряд.
Лишь бы не было другого разделителя (не решётки), которые помешали бы конвертированию строки в целое.
Более общие решения для любого набора разделителей можно без труда найти на форуме.
Было не раз. Например: Разобрать строку
21 авг 09, 14:23    [7566974]     Ответить | Цитировать Сообщить модератору
 Re: Удаление символов в строке  [new]
Anddros
Member

Откуда:
Сообщений: 1077
iap
Anddros, зачем курсор, слюшай?!
...

Дык, а если должна остаться строка, состоящая не из ОДНОГО кода?

Поглядим, что вернет запрос на маааленькое изменение в данных:
insert into #a1 (tab, kod) (select 10,1)
insert into #a1 (tab, kod) (select 10,10)
insert into #a1 (tab, kod) (select 12,1)
insert into #a1 (tab, kod) (select 12,3)

insert into #a2 (tab, [skod]) (select 10,'1#3#10#')
insert into #a2 (tab, [skod]) (select 12,'1#3#6#7#')

Вот это:
10	3#
12 6#
12 7#
Впрочем, может как раз это автору и надо.

А объединить произвольное число записей в строку на 2000-м - опять-таки приходим к курсорам, циклам, функциям или способу ChA.

ChA,
Хоть и стоит на #t
PRIMARY KEY(tab, kod)
, я б все равно не поручился, что будет ВСЕГДА, на ЛЮБЫХ ДАННЫХ апдейтиться именно в определенном порядке. А иначе - все расползется. То, что хорошо работает для одного значения, не всегда пригодно для таблицы. В продакшн я такое точно б не поставил. :)
21 авг 09, 15:16    [7567447]     Ответить | Цитировать Сообщить модератору
 Re: Удаление символов в строке  [new]
ChA
Member

Откуда: Москва
Сообщений: 10985
Anddros
я б все равно не поручился, что будет ВСЕГДА, на ЛЮБЫХ ДАННЫХ апдейтиться именно в определенном порядке. А иначе - все расползется. То, что хорошо работает для одного значения, не всегда пригодно для таблицы. В продакшн я такое точно б не поставил.
Формально, Вы правы, никаких гарантий нет. Но у таблицы есть кластерный индекс, при этом уникальный. По плану можно увидеть обновление происходит в порядке Clustered index scan. Здесь можно обнаружить интересное обсуждение на эту тему.

P.S. Лично я не брезгую этим способом при формировании отчётности, жалоб не было.
P.P.S. Нетрудно вставить дополнительную проверку на последовательность прохода и обновлять другим способом в случае ошибки. Соблазн этого способа в том, что у него практически нет конкурентов по скорости обновления в случае необходимости получения кумулятивных значений, особенно на больших таблицах.
21 авг 09, 16:45    [7568154]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить