Блог

Главная - Вопросы по программированию - Извлечение значений из столбца в SQL Server

Извлечение значений из столбца в SQL Server

#sql #sql-server #database

#sql #sql-сервер #База данных

Вопрос:

Как извлечь следующие значения из столбца в SQL Server:

 "ICMP (type: 8 / subtype: 0)" -----> "8" and "0"
"ICMP (type: 0 / subtype: 19)" -----> "0" and "19"
"ICMP (type: 11 / subtype: 5)" -----> "11" and "5"
"ICMP (type: 12 / subtype: 30)" -----> "12" and "30"

Я пробовал разные функции — charindex(), left(), right(), но у меня не получилось.

Комментарии:

1. Ошибка заключается в хранении нескольких фрагментов данных в одном поле. Это нарушает самое фундаментальное правило проектирования и нуждается в исправлении. Проанализируйте данные, прежде чем вводить их в базу данных. Намного проще анализировать данные, например, на C #, используя регулярное выражение или специальный анализатор, чем выполнять даже простые манипуляции со строками в T-SQL

2. Запрос этих данных не может использовать какие-либо индексы, поэтому каждый раз, когда вы хотите прочитать эти значения, вам придется сканировать и анализировать всю таблицу. Синтаксический анализ этого поля имеет смысл только в том случае, если вы хотите перейти к лучшей схеме и перенести старые данные. В любом другом случае быстрее использовать другой язык для синтаксического анализа строк. Даже R или Python в SQL Server 2016 и более поздних версиях.

Ответ №1:

SQL Server не силен в манипулировании строками. Но cross apply немного помогает:

 select left(v.str1, charindex(' ', v.str1)) as type, replace(v.str2, ')', '') as subtype
from (values ('ICMP (type: 8 / subtype: 0)'), ('ICMP (type: 12 / subtype: 30)')) t(field) cross apply
     (values ( stuff(field, 1, charindex('type:', field)   5, ''),
               stuff(field, 1, charindex('subtype:', field)   8, '')
             )
     ) v(str1, str2);

Комментарии:

1. Я бы сказал, что использование сценариев SQL Server на языке R или Python и регулярных выражений было бы проще и быстрее, но требуемый шаблон может быть почти таким же запутанным для нового пользователя

2. Это ужасно элегантно.

3. @PanagiotisKanavos . . . Я бы предположил, что накладные расходы на запуск Python или R будут значительно больше, чем накладные расходы на встроенные строковые функции SQL Server. Если вы знаете по-другому, мне было бы интересно узнать.

Метки: