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

Откуда: Minsk Power Station
Сообщений: 508
Здраствуйте!
Есть поле Numeric(9,0) примерно с такими значениями:
398470234
230948029
234898234
023402309
122318090
323402311

как отобрать записи, где есть последовательное вхождение цифр, например '023'?
чтобы результат такой был:
398470234
023402309
323402311

Слышал от авторитетов SQL, что Like слишком затратная операция. А ещё есть способы другие?

Хочу сделать подгружаемый список значений, хотя бы первых 10-20 записей, как вот на телефонах Самсунг, там при наборе цифр отображаются номера, которые присутствуют в журнале звонков.
Но допустим, на 5.000 номеров записей, притормаживает как-то, мне показалось. Отбираются очень много записей, а смысла выводить такую огромную простыню - не вижу. Как лучше поступить?
3 сен 15, 17:37    [18105265]     Ответить | Цитировать Сообщить модератору
 Re: Kак отобрать записи, где есть вхождение последовательности цифр, кроме как Like?  [new]
Konst_One
Member

Откуда:
Сообщений: 11538
palladin600
023402309


такого не может быть, если поле целое числовое
3 сен 15, 18:02    [18105329]     Ответить | Цитировать Сообщить модератору
 Re: Kак отобрать записи, где есть вхождение последовательности цифр, кроме как Like?  [new]
palladin600
Member

Откуда: Minsk Power Station
Сообщений: 508
Konst_One
palladin600
023402309


такого не может быть, если поле целое числовое


Да, это очень плохо, много номеров начинаются с 0. Получается, поле numeric не совсем подходит? Потому что отбор нужен как есть, т.е. с первым нулём.
3 сен 15, 18:34    [18105350]     Ответить | Цитировать Сообщить модератору
 Re: Kак отобрать записи, где есть вхождение последовательности цифр, кроме как Like?  [new]
palladin600
Member

Откуда: Minsk Power Station
Сообщений: 508
Все. Добавил ко всем номерам спереди единицу. Теперь начинающихся на "0" записей нет. Давайте идеи. Это же для удобства делается. Автоматизация.
3 сен 15, 21:11    [18105632]     Ответить | Цитировать Сообщить модератору
 Re: Kак отобрать записи, где есть вхождение последовательности цифр, кроме как Like?  [new]
komrad
Member

Откуда:
Сообщений: 5252
palladin600
Все. Добавил ко всем номерам спереди единицу. Теперь начинающихся на "0" записей нет. Давайте идеи. Это же для удобства делается. Автоматизация.


полет мысли:
1) добавить computed column = convert(numeric(9,0),replace(convert(varchar(10),FIELD),'023',''))
2) искать по принципу FIELD <> computed column
3 сен 15, 21:32    [18105673]     Ответить | Цитировать Сообщить модератору
 Re: Kак отобрать записи, где есть вхождение последовательности цифр, кроме как Like?  [new]
Makar4ik
Member

Откуда: Когда-то были Лужки, а теперь Бордюр-Сити.
Сообщений: 2676
palladin600
Слышал от авторитетов SQL, что Like слишком затратная операция.
Затратная. Потому что приводит к скану всей таблицы.
Но ваша задача иначе и не решается.
так что проще всего хранить номера в текстовом виде. Без всяких лишних единичек в начале.
И без convert, использовать like в лоб.

Для MSSQL, like по 5000 записей - ерунда полная.
3 сен 15, 23:02    [18105916]     Ответить | Цитировать Сообщить модератору
 Re: Kак отобрать записи, где есть вхождение последовательности цифр, кроме как Like?  [new]
palladin600
Member

Откуда: Minsk Power Station
Сообщений: 508
Так, ну ясно. Большое спасибо за ответы. Так и поступлю. В текстовом поле буду хранить. 5.000 это первоначальное количество. Я перегоню еще порядка 130.000 из старенькой бд.
3 сен 15, 23:27    [18105967]     Ответить | Цитировать Сообщить модератору
 Re: Kак отобрать записи, где есть вхождение последовательности цифр, кроме как Like?  [new]
Makar4ik
Member

Откуда: Когда-то были Лужки, а теперь Бордюр-Сити.
Сообщений: 2676
palladin600,

130к - это уже заметно серьёзнее.
но с условием поиска подстроки, вариантов как бы мало.
3 сен 15, 23:37    [18105991]     Ответить | Цитировать Сообщить модератору
 Re: Kак отобрать записи, где есть вхождение последовательности цифр, кроме как Like?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
palladin600,

Ну можно еще разбить 398470234
в отдельной таблице на:
398470234
98470234
8470234
470234
70234
0234
234
34
4
создать индекс и делать поиск как LIKE '023%' вместо LIKE '%023%'.

Гиммороя будет больше, места тоже займет прилично, но зато поиск будет очень быстрый.
3 сен 15, 23:47    [18106016]     Ответить | Цитировать Сообщить модератору
 Re: Kак отобрать записи, где есть вхождение последовательности цифр, кроме как Like?  [new]
Makar4ik
Member

Откуда: Когда-то были Лужки, а теперь Бордюр-Сити.
Сообщений: 2676
Mind,

Согласен, врубается индекс по таблице, но она в 10-20 раз больше, и её надо перестраивать с каждым чихом.
OLTP просядет.
4 сен 15, 00:04    [18106043]     Ответить | Цитировать Сообщить модератору
 Re: Kак отобрать записи, где есть вхождение последовательности цифр, кроме как Like?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Makar4ik,

Зависит от того, сколько там того OLTP, то есть насколько часто обновляется оригинальная таблица. Это всегда trade off. Быстрее поиск - медленее обновления и больше размер, и наоборот.
4 сен 15, 01:01    [18106150]     Ответить | Цитировать Сообщить модератору
 Re: Kак отобрать записи, где есть вхождение последовательности цифр, кроме как Like?  [new]
Makar4ik
Member

Откуда: Когда-то были Лужки, а теперь Бордюр-Сити.
Сообщений: 2676
Mind,

Опять же согласен на все 100.
Если OLAP востребован каждую секунду, а дерево перестраивают раз в день, то раз в день - можно и подождать 15 секунд.
4 сен 15, 02:22    [18106236]     Ответить | Цитировать Сообщить модератору
 Re: Kак отобрать записи, где есть вхождение последовательности цифр, кроме как Like?  [new]
palladin600
Member

Откуда: Minsk Power Station
Сообщений: 508
Ага, значит он затратный, когда LIKE в обе стороны %023%, а когда в одну то норма. Тогда наверное ещё есть смысл делать LIKE от 4-х символов. Думаю, что дробить тогда надо меньше, соответственно и объём чуточку уменьшится.
398470234
98470234
8470234
470234
70234
0234
4 сен 15, 10:12    [18106954]     Ответить | Цитировать Сообщить модератору
 Re: Kак отобрать записи, где есть вхождение последовательности цифр, кроме как Like?  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
palladin600
Слышал от авторитетов SQL, что Like слишком затратная операция. А ещё есть способы другие?


Вот такой способ вам подойдёт?

DECLARE @T TABLE (N INT )

INSERT INTO @T(N) VALUES
(398470234), 
(230948029), 
(234898234), 
(023402309), 
(122318090), 
(323402311)


DECLARE @D TABLE (D INT )
INSERT INTO @D VALUES (1), (10), (100), (1000), (10000), (100000), (1000000)

DECLARE @VALUE INT = 023

SELECT DISTINCT N FROM @T, @D
WHERE (N / D) % 1000 = @Value 
4 сен 15, 11:29    [18107465]     Ответить | Цитировать Сообщить модератору
 Re: Kак отобрать записи, где есть вхождение последовательности цифр, кроме как Like?  [new]
Konst_One
Member

Откуда:
Сообщений: 11538
проверьте сперва, что у вас в таблице записалось:

DECLARE @T TABLE (N INT )

INSERT INTO @T(N) VALUES
(398470234), 
(230948029), 
(234898234), 
(023402309), 
(122318090), 
(323402311)

select * from @T
4 сен 15, 11:31    [18107474]     Ответить | Цитировать Сообщить модератору
 Re: Kак отобрать записи, где есть вхождение последовательности цифр, кроме как Like?  [new]
Konst_One
Member

Откуда:
Сообщений: 11538
у ТС явно не числовое поле, а какой-нибудь varchar. Он до сих пор скрипты не предоставил.
4 сен 15, 11:32    [18107479]     Ответить | Цитировать Сообщить модератору
 Re: Kак отобрать записи, где есть вхождение последовательности цифр, кроме как Like?  [new]
a_voronin
Member

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

Если вы про лидирующий 0, то я прекрасно понимаю, что его не будет. То, что я написал, прекрасно работает не смотря на отсутствие лидируещего 0. Число цифр определяется этой константой.
4 сен 15, 12:23    [18107851]     Ответить | Цитировать Сообщить модератору
 Re: Kак отобрать записи, где есть вхождение последовательности цифр, кроме как Like?  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
(N / D) % 1000 = @Value
4 сен 15, 12:26    [18107880]     Ответить | Цитировать Сообщить модератору
 Re: Kак отобрать записи, где есть вхождение последовательности цифр, кроме как Like?  [new]
Konst_One
Member

Откуда:
Сообщений: 11538
да, возможно ТСу такой вариант подойдёт.
4 сен 15, 12:28    [18107888]     Ответить | Цитировать Сообщить модератору
 Re: Kак отобрать записи, где есть вхождение последовательности цифр, кроме как Like?  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
Konst_One
да, возможно ТСу такой вариант подойдёт.


Вот такой вариант даже оптимальней

DECLARE @T TABLE (N INT )

INSERT INTO @T(N) VALUES
(398470234), 
(230948029), 
(234898234), 
(023402309), 
(122318090), 
(323402311)


DECLARE @D TABLE (D INT )
INSERT INTO @D VALUES (1), (10), (100), (1000), (10000), (100000), (1000000)

DECLARE @VALUE INT = 023

SELECT SUBSTRING(CAST(1000000000 + N AS CHAR(10)), 2, 9) -- типа LPAD
FROM @T
CROSS APPLY (SELECT TOP 1 D FROM @D WHERE (N / D) % 1000 = @Value) D
4 сен 15, 12:33    [18107923]     Ответить | Цитировать Сообщить модератору
 Re: Kак отобрать записи, где есть вхождение последовательности цифр, кроме как Like?  [new]
Makar4ik
Member

Откуда: Когда-то были Лужки, а теперь Бордюр-Сити.
Сообщений: 2676
palladin600
Ага, значит он затратный, когда LIKE в обе стороны %023%, а когда в одну то норма. Тогда наверное ещё есть смысл делать LIKE от 4-х символов. Думаю, что дробить тогда надо меньше, соответственно и объём чуточку уменьшится.
398470234
98470234
8470234
470234
70234
0234
Не в одну, а с начала строки. Так поиск по индексу реализован. С начала строки.
5 сен 15, 00:32    [18111894]     Ответить | Цитировать Сообщить модератору
 Re: Kак отобрать записи, где есть вхождение последовательности цифр, кроме как Like?  [new]
Makar4ik
Member

Откуда: Когда-то были Лужки, а теперь Бордюр-Сити.
Сообщений: 2676
Makar4ik
palladin600
Ага, значит он затратный, когда LIKE в обе стороны %023%, а когда в одну то норма. Тогда наверное ещё есть смысл делать LIKE от 4-х символов. Думаю, что дробить тогда надо меньше, соответственно и объём чуточку уменьшится.
398470234
98470234
8470234
470234
70234
0234
Не в одну, а с начала строки. Так поиск по индексу реализован. С начала строки.
...Вернее, так B-Tree строится по индексу...
5 сен 15, 00:35    [18111895]     Ответить | Цитировать Сообщить модератору
 Re: Kак отобрать записи, где есть вхождение последовательности цифр, кроме как Like?  [new]
palladin600
Member

Откуда: Minsk Power Station
Сообщений: 508
a_voronin
Konst_One
да, возможно ТСу такой вариант подойдёт.


Вот такой вариант даже оптимальней

DECLARE @T TABLE (N INT )

INSERT INTO @T(N) VALUES
(398470234), 
(230948029), 
(234898234), 
(023402309), 
(122318090), 
(323402311)


DECLARE @D TABLE (D INT )
INSERT INTO @D VALUES (1), (10), (100), (1000), (10000), (100000), (1000000)

DECLARE @VALUE INT = 023

SELECT SUBSTRING(CAST(1000000000 + N AS CHAR(10)), 2, 9) -- типа LPAD
FROM @T
CROSS APPLY (SELECT TOP 1 D FROM @D WHERE (N / D) % 1000 = @Value) D


Если DECLARE @VALUE INT = 023, то да, стремительно работает. А что ещё надо править, чтобы больше символов искалось?
Потому что если поставить DECLARE @VALUE INT = 0234 (или больше символов), то ничего не ищет.
5 сен 15, 15:17    [18112758]     Ответить | Цитировать Сообщить модератору
 Re: Kак отобрать записи, где есть вхождение последовательности цифр, кроме как Like?  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
palladin600
a_voronin
пропущено...


Вот такой вариант даже оптимальней

DECLARE @T TABLE (N INT )

INSERT INTO @T(N) VALUES
(398470234), 
(230948029), 
(234898234), 
(023402309), 
(122318090), 
(323402311)


DECLARE @D TABLE (D INT )
INSERT INTO @D VALUES (1), (10), (100), (1000), (10000), (100000), (1000000)

DECLARE @VALUE INT = 023

SELECT SUBSTRING(CAST(1000000000 + N AS CHAR(10)), 2, 9) -- типа LPAD
FROM @T
CROSS APPLY (SELECT TOP 1 D FROM @D WHERE (N / D) % 1000 = @Value) D


Если DECLARE @VALUE INT = 023, то да, стремительно работает. А что ещё надо править, чтобы больше символов искалось?
Потому что если поставить DECLARE @VALUE INT = 0234 (или больше символов), то ничего не ищет.


Что ж вы такой беспомощный ? Разберитесь в принципе работы скрипта

DECLARE @T TABLE (N INT )

INSERT INTO @T(N) VALUES
(398470234), 
(230948029), 
(234898234), 
(023402309), 
(122318090), 
(323402311)


DECLARE @D TABLE (D INT )
INSERT INTO @D VALUES (1), (10), (100), (1000), (10000), (100000)

DECLARE @VALUE INT = 0234

SELECT SUBSTRING(CAST(1000000000 + N AS CHAR(10)), 2, 9) -- типа LPAD
FROM @T
CROSS APPLY (SELECT TOP 1 D FROM @D WHERE (N / D) % 10000 = @Value) D


Универсальную функцию сможете сами написать или мне это за вас сделать?

Я убрал ", (1000000)"
и заменил "% 1000" на "% 10000"
5 сен 15, 16:28    [18112927]     Ответить | Цитировать Сообщить модератору
 Re: Kак отобрать записи, где есть вхождение последовательности цифр, кроме как Like?  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
palladin600
то да, стремительно работает
Проверка "стремительности"
+
use tempdb;
go

create table dbo.t (id int primary key, v as right('000000000' + cast(id as varchar(10)), 9));
go

insert into dbo.t
select top (1000000)
 row_number() over (order by (select 1))
from
 master.dbo.spt_values a cross join
 master.dbo.spt_values b;
go

declare @d table (d int);
insert into @d values (1), (10), (100), (1000), (10000), (100000);

declare @id int, @v int = 23;

set statistics time on;

select
 @id = id
from
 dbo.t
where
 v like '%023%' option (maxdop 1);

select
 @id = id
from
 dbo.t cross apply
 (select top 1 d from @d where (t.id / d) % 1000 = @v) d
option
 (maxdop 1);

set statistics time off;
go

drop table dbo.t;
go

Время работы SQL Server:
Время ЦП = 1139 мс, затраченное время = 1145 мс.

Время работы SQL Server:
Время ЦП = 4680 мс, затраченное время = 4716 мс.
5 сен 15, 17:22    [18113083]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить