Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Проектирование БД [игнор отключен] [закрыт для гостей] / Поиск подстроки по строке (не так просто, как кажется) / 6 сообщений из 6, страница 1 из 1
22.04.2009, 00:42
    #35945782
Alex Lavrinenko
Alex Lavrinenko
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск подстроки по строке (не так просто, как кажется)
Всем доброго времени суток!

Может повторяюсь, но полтора часа поиска по форуму ничего не дали. Проблема:
конструкция запроса SELECT string FROM table WHERE string LIKE 'substring%'; -> выберет строку string из табл. table, совпавшую по началу с substring. У меня же string и substring поменяны местами, т.е. в таблице table хранятся варианты substring, а string подставляется в запрос. Т.е.:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
таблица table
 id | substring |
----+-----------+
   5  |       0000  |
   2  |       0100  |
   7  |      0aa0 |
   3  |       ccb |
надо выбрать из таблицы table поле id только той записи, которая совпадёт как подстрока со строкой из запроса. То есть нужен запрос наподобие:
Код: plaintext
1.
SELECT id FROM table WHERE '0aa0some fucking text of string to match' LIKE substring;
который должен вернуть 7 . Проблема в том, что приаттачить '%' к концу substring в запросе нельзя - ошибка синтаксиса:
Код: plaintext
1.
2.
3.
ERROR:  тип "substring" не существует
СТРОКА  1 :SELECT id FROM table WHERE '0aa0Some  fucking' LIKE substring'%';
                                                             ^
В случае, если в поле substring в таблице table будут лежать данные с символом % на конце, то всё работает:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
 id |  substring |
----+------------+
   5  |       0000 % |
   2  |       0100 % |
   7  |      0aa0% |
   3  |       ccb% |
SELECT id FROM table WHERE '0aa0some fucking text of string to match' LIKE substring;
 id
----
   7
Однако этот вариант КРАЙНЕ нежелателен к реализации, поскольку из данных колонки substring могут формироваться строки по другим запросам слиянием прочих таблиц.

Также обязательное условие - если в таблице будут значения substring 0aa0 и 0aa , необходимо чтобы совпадение происходило по наидлиннейшему, т.е. по 0aa0 , и возвращало id только этого совпадения.

Подскажите, кто сталкивался - я уже всю голову сломал :(
БД - PostgreSQL 8.3.5/Linux
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.04.2009, 00:44
    #35945784
ChA
ChA
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск подстроки по строке (не так просто, как кажется)
Вам сюда , однако.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.04.2009, 00:52
    #35945792
Ёш
Ёш
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск подстроки по строке (не так просто, как кажется)
Alex LavrinenkoПроблема в том, что приаттачить '%' к концу substring в запросе нельзя - ошибка синтаксиса:
Код: plaintext
1.
2.
3.
ERROR:  тип "substring" не существует
СТРОКА  1 :SELECT id FROM table WHERE '0aa0Some  fucking' LIKE substring'%';
                                                             ^


строки в PotgsreSQL соединяются оператором ||
попробуйте:
Код: plaintext
SELECT id FROM table WHERE '0aa0Some  fucking' LIKE substring || '%';
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.04.2009, 01:13
    #35945802
Alex Lavrinenko
Alex Lavrinenko
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск подстроки по строке (не так просто, как кажется)
Ёшстроки в PotgsreSQL соединяются оператором ||
попробуйте:
Код: plaintext
SELECT id FROM table WHERE '0aa0Some  fucking' LIKE substring || '%';

Спасибо за оперативный ответ, именно эту конструкцию пробовал в самом начале - возвращала несколько строк. Сейчас создал новую тестовую таблицу и сразу нашёл ошибку - если в поле substring будет не NULL (а например ''), то эта строка тоже попадёт под критерий отбора. А у меня как раз в таблице несколько строк с (как я думал) пустыми значениями substring - вот они-то и возвращались...

Походу придётся пересоздать таблицу с substring varchar(n) DEFAULT NULL и вместо '' явно инсертить NULL для substring, если там ничего не должно быть. Ну или буду сейчас реализовывать longest match limit 1, он точно вернёт только одно наидлиннейшее совпадение, как раз по заданию.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.04.2009, 11:49
    #35946474
Alexandr Nikolaev
Alexandr Nikolaev
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск подстроки по строке (не так просто, как кажется)
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
BEGIN
  declare local temporary table #TABLE("ID" integer NOT NULL DEFAULT autoincrement, "SUBSTRING" long varchar NOT NULL DEFAULT '') not transactional;
  insert into #TABLE(SUBSTRING) values('0001');
  insert into #TABLE(SUBSTRING) values('0010');
  insert into #TABLE(SUBSTRING) values('0100');
  insert into #TABLE(SUBSTRING) values('1000');
  insert into #TABLE(SUBSTRING) values('ccb');
  insert into #TABLE(SUBSTRING) values('0aa');
  insert into #TABLE(SUBSTRING) values('0aa0');
  insert into #TABLE(SUBSTRING) values('0a');

  select top  1 
    #TABLE."ID",
    #TABLE."SUBSTRING"
  from
    #TABLE
  where
    CHARINDEX(#TABLE."SUBSTRING", '0aa0Some  fucking') =  1 
  order by
    CHAR_LENGTH(#TABLE."SUBSTRING") desc;
END
C уважением,
AlexandrN©
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.04.2009, 13:09
    #35946812
Alex Lavrinenko
Alex Lavrinenko
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Поиск подстроки по строке (не так просто, как кажется)
Alexandr Nikolaev
Код: plaintext
1.
2.
3.
BEGIN
... 
END
C уважением,
AlexandrN©
Тёзка, спасибо. Ваш вариант сложнее :-) Проблему уже решил, таким образом:
Код: plaintext
1.
SELECT id FROM table WHERE '0aa0Some  fucking' LIKE substring || '%' ORDER BY LENGTH(substring) DESC LIMIT  1 ;
Ну и помимо этого пересоздал таблицу table:
Код: plaintext
1.
2.
3.
4.
CREATE TABLE table1(
        id serial,
        substring varchar( 10 ) DEFAULT NULL
);
Собственно косяку способствовал и клиент (CGI C app): добавление в таблицу (а также и апдейт) производились так:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
/* INSERT new record */
...
q_result = db_calls->query(db_conn_no, DBMS_COMMAND, "INSERT INTO table1 (substring) VALUES ('%s')",
cgi_get_value("substring",  0 ) ? cgi_get_value("substring",  0 ) : "");

/* UPDATE existing record */
...
q_result = db_calls->query(db_conn_no, DBMS_COMMAND, "UPDATE table1 SET substring = '%s' WHERE id = %s",
cgi_get_value("substring",  0 ) ? cgi_get_value("substring",  0 ) : "", table_id);
...
Так вот, если substring при апдейте или инсерте не был указан в HTML-форме, то инсертились/апдейтились ''. Если вставить в код проверки наличия substring параметр NULL при отсутствии значения, то получалось INSERT/UPDATE ... 'NULL', что есть некорректно. Посему переделал также клиента:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
char *substr = NULL;

/* if HTML form field is not empty, create properly formatted (quoted) value for INSERT/UPDATE */
if(cgi_get_value("substr",  0 ))
{
	substr = malloc(strlen(cgi_get_value("substr",  0 )) +  2 );
	sprintf(substr, "'%s'", cgi_get_value("substr",  0 ));
}

/* INSERT new record */
...
q_result = db_calls->query(db_conn_no, DBMS_COMMAND, "INSERT INTO table1 (substring) VALUES (%s)",
substr ? substr : "NULL");

/* UPDATE existing record */
...
q_result = db_calls->query(db_conn_no, DBMS_COMMAND, "UPDATE table1 SET substring = %s WHERE id = %s",
substr ? substr : "NULL", table_id);
...
if(substr)
	free(substr);
Таким образом мы дважды избегаем наступления на эти грабли: сначала правильно пишем NULL (а не '') в БД если substr не задан, и потом сортировкой по макс. длине с лимитом. В принципе, с переделкой таблицы и клиента можно было не заморачиваться, ORDER BY и LIMIT 1 делают своё дело. Однако на большом кол-ве записей в таблице явный NULL КМК ускорит процесс обработки ORDER BY, что есть гуд.

В целом, с граблей слез, проблема решена, тему можно в фак :-)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Проектирование БД [игнор отключен] [закрыт для гостей] / Поиск подстроки по строке (не так просто, как кажется) / 6 сообщений из 6, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 570ms
Закрыть
start [/forum/topic.php?fid=32&mobile=1&tid=1543293]:
 0ms
get settings:
 8ms
get forum list:
 15ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 165ms
get topic data:
 9ms
get forum data:
 2ms
get page messages:
 36ms
get tp. blocked users:
 1ms
others: 328ms
total:  570ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]