Как обойтись без курсора?

abursh
Дата: 21.03.2001 14:59:47
Кто может придумать, как решить проблему, описанную
ниже, без использования курсора?
Итак, есть таблица TAB1, а в ней поле STRING varchar(320).
Поле это может содержать символы, которые надо заменить на пробелы.
На пробелы следует заменить символы, код ASCII которых
или равен значениям списка (127, 126, 94), или <= 31, или >=154.
Каждый символ может повторяться в строке сколько угодно раз.

Вроде ерунда сущая, но не могу придумать, как без курсора решить сию задачу. А с курсором работает медленно до неприличия.
Буду признателен за любую подсказку.
Дед Маздай
Дата: 21.03.2001 15:59:14
Примерно так:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ConvertString]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[ConvertString]
go
create function ConvertString(@s varchar(320))
returns varchar(320) as begin
declare @s1 varchar(320), @c char(1), @n tinyint, @i smallint
select @s1 = '', @i = 1
while @i <= len(@s) begin
select @c = substring(@s, @i, 1), @n = ascii(@c)
if (@n <= 31 or @n in (94, 126, 127) or @n >= 154) set @c = ' '
select @s1 = @s1 + @c, @i = @i + 1
end
return @s1
end

и затем

update tab1 set string = ConvertString(string)
SergSuper
Дата: 21.03.2001 16:03:32
А курсоры то тут как помогут?

если стоит 2000, то можно написать функцию

самый быстрый способ будет если написать как-то

update TAB1
set fld=
replace(
replace(
replace(fld,char(127),' '),
char(126),' '),
char(94),' ')

ну и так еще 144 раза
Michael+Hopgarden
Дата: 22.03.2001 14:42:12
Развивая SergSuper вот такой изврат получился для 7:

set nocount on
create table #TAB1(STRING varchar(320))
declare @i as int, @s as varchar(5120), @e as varchar(3072)
select @i = 1, @s = ''
while @i < 256 select @s = @s + char(@i), @i = @i + 1
insert into #TAB1(STRING) values(@s)
insert into #TAB1(STRING) values(reverse(@s))
select @i = 1, @s = 'replace('
while @i < 136 select @s = @s + 'replace(', @i = @i + 1
select @i = 1, @e = ''
select @e = ', char(94), char(32))'
select @e = @e + ', char(126), char(32))'
select @e = @e + ', char(127), char(32))'
while @i < 32 select @e = @e +', char('+cast(@i as varchar(3))+'), char(32))', @i = @i + 1
select @i = 154
while @i < 256 select @e = @e +', char('+cast(@i as varchar(3))+'), char(32))', @i = @i + 1
select @s = 'update #TAB1 set STRING = ' + @s + 'STRING' + @e
select STRING as before from #TAB1
exec(@s)
select STRING as after from #TAB1
drop table #TAB1
set nocount off
SergSuper
Дата: 22.03.2001 15:31:47
Еу раз заинтересовал мой вариант, осмелюсь предложить еще, менее извращенный, но более медленный

надо создать таблицу со всеми символами

create table #t(c char)
insert #t select char(94)
insert #t select char(126)
...
insert #t select char(255)

и далее такой цикл:

while exists(select * from #t t, #TAB1 b where STRING like '%'+c+'%')
update #TAB1 set STRING=replace(STRING,c,' ')
from #TAB1 b, #t t
where exists(select * from #t t1 where STRING like '%'+t1.c+'%')

для ускорения в exists(...) можно таблицу #t не ставить, а написать типа "where STRING Like '%['+char(0)+'-'+char(31)+']%'", но тогда универсальность пропадает(мало ли придётся добавить еще символ)
abursh
Дата: 22.03.2001 16:03:36
Простите, забыл указать, что речь идет, конечно, о MSSQL 7.
В MSSQL 2000 с помощью, пользовательской функции, задача решается тривиально.

Вариант с занесением в таблицу кодов, который предлагает SERGSUPER мне в голову приходил, но перспектива заполнения таблицы сразу его и умертвила.

Вариант Михаила с первого взгляда не понял, но - спасибо, подумаю.

Всем, не поленившимся написать - благодарен. Спасибо.
АБ
Andrew K
Дата: 23.03.2001 15:34:30
Предположим таблица TAB1 имеет первичным ключом поле record_id какого-либо числового типа. Тогда пишем так.

declare @rc int, @rid int, @s varchar(320)

select @rid=min(record_id)-1 from TAB1

select @rc=@@rowcount
if @rc=0 return

while 1=1
begin
select top 1
@rid=@record_id, --продвижение по циклу
@s=s
from TAB1
where record_id>@rid
order by record_id --top 1 и order by обязательно!!!

select @rc=@@rowcount
if @rc=0 break

--Здесь обрабатываем переменную @s точно так же, как в функции от Деда Маздая

update TAB1 set s=@s where record_id=@rid --Сохраняем результат обработки
end

Все. Работает быстрее, чем курсор. При наличии составного первичного ключа из числовых полей просто используются вложенные циклы аналогичные вышеописанным. На нечисловых типах данных в первичном ключе техника еще не использовалась (не столкнулись с необходимостью), но думаю, что и в этом случае можно что-то сделать.
SergSuper
Дата: 23.03.2001 16:47:16
Насчет того как заполнить таблицу.

Вообще хорошо бы иметь в базе таблицу с последовательностью чисел, лучше всего от 0 до 999. Не надо смеяться, оно вам очень может понадобиться. Но можно обойтись и от 0 до 9.

Допустим у нас есть таблица от 0 до 9

create table #i(num int)
insert #i select 0
insert #i select 1
insert #i select 2
insert #i select 3
insert #i select 4
insert #i select 5
insert #i select 6
insert #i select 7
insert #i select 8
insert #i select 9

тогда таблица заполняется просто
create table #c(c char)
insert #c select char(i2.i*100+i1.i*10+i0.i)
from #i i0,#i i1,#i i2
where i2.i*100+i1.i*10+i0.i <=31
or i2.i*100+i1.i*10+i0.i >=154
or i2.i*100+i1.i*10+i0.i in (127, 126, 94)

если бы была таблица от 0 до 999 выглядело бы чуть покрасивше:
insert #c select char(i)
from #i where i <=31 or i>=154 or i in (127, 126, 94)


далее, возвращаясь к предыдущему мессаджу, если у нас есть такая таблица, то переменную можно проапдейтить намного проще, чем у Деда Маздая:

select @s=replace(@s,c,' ') from #c

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

С приветом Сергей

Попробуйте все способы и напишите время выполнения каждого, если не трудно.
abursh
Дата: 25.03.2001 15:56:01
К сожалению, все способы попробовать не смогу. В седьмом эскуэле нет пользовательских функций, а в моем файле нет поля IDENT, a primery key состоит из двух полей: код поставщика (int) + код товара поставщика (char30).
Поэтому выбор остаетсыа меж вариантом с таблицей с кодами (предложил SergSuper), и вариантом, предложенным Michael+Hopgarden. Вариант с таблитзеи несомненно более гибок, и столь же несомненно, более медленнен, а от варианта
Michael+Hopgarden я, признаться, просто затащился от удовольствия, когда врубился в его смысл. Его я тут же прогнал, и .. вылетел по dublicate key.
Но это уже вина моя, так как по тупости о таком исходе, ставя задачу, я не подумал. Всем спасибо, а перед Michael+Hopgarden снимаю шляпу.