Извлечь строку для определенных символов SQL Server
Попытка извлечь определенную строку из столбца varchar в SQL Server. И тогда из этой строки мне нужны только последние 4 цифры.
Я пробовал substring и patindex, но не могу понять, как получить последние 4 цифры.
Это была одна попытка, но она возвращает только первую часть текста:
SUBSTRING([column], (PATINDEX('%ID0%-[0-9][0-9][0-9][0-9][0-9]%',[column])),9)
Вот некоторые примеры данных:
Brian Larry, Hays Hill XX, ID007370, Option code - 0123 4567, AAA - XX
ID007366 - Dave Jones - XX - Option Code 0121 9999
ID00 7120 Brian Smith XX Branded company
ID07113 Gary Barnes - LLL 0123 9111 AAA LLL
ID00 7120 Charles Old XX Recall operation
ID0007439 - Kerry Hill - Maidstone XX - Option Code 0124 234 BBB XX
ID006817 Paul George Jackson 1234 8464 AAA Recall operation
Мне нужно извлечь номер ID00000, но затем вернуть только последние 4 цифры. Так что из ID007370 я хочу только вернуться 7370. Идентификационные номера могут различаться по длине.
Расскажи мне об этом :)
Что еще хуже, значения ID даже не всегда появляются в одном и том же месте строки. Даже при полной поддержке регулярных выражений это непростая задача. Исправьте данные перед их переносом в SQL Server.
Здесь вам нужно вернуться к источнику; необходимо устранить то, что когда-либо создает эти ужасные данные. Ссора с Гэри Барнсом, честно говоря, ужасна; нет определенного разделителя. Это не может быть один пробел, потому что тогда Gary и Barnes будут разными столбцами, но тогда это не может быть дефис (-), потому что тогда у вас будет одно значение столбца ID07113 Gary Barnes. У Пола Джорджа аналогичная проблема: пробел является одновременно разделителем и внутри имени. Данные ужасны; это проблема, которую необходимо решить. Исправьте это, и получение идентификатора станет тривиальным.
Ответы 3
Что-то вроде этого работает:
SELECT RIGHT(SUBSTRING(string_nonspace, y.start, PATINDEX('%[^0-9]%', STUFF(string_nonspace, 1, start + 1, '')) + 1), 4)
FROM (
VALUES (N'Brian Larry, Hays Hill XX, ID007370, Option code - 0123 4567, AAA - XX')
, (N'ID007366 - Dave Jones - XX - Option Code 0121 9999')
, (N'ID00 7120 Brian Smith XX Branded company')
, (N'ID07113 Gary Barnes - LLL 0123 9111 AAA LLL')
, (N'ID00 7120 Charles Old XX Recall operation')
, (N'ID0007439 - Kerry Hill - Maidstone XX - Option Code 0124 234 BBB XX')
, (N'ID006817 Paul George Jackson 1234 8464 AAA Recall operation')
) t (col1)
CROSS APPLY (
SELECT replace(col1, ' ', '') AS string_nonspace
) x
CROSS APPLY (
SELECT PATINDEX('%ID[0-9]%', string_nonspace) AS start
) y
Выход:
Я удаляю пробелы, а затем нахожу начальную позицию по PATINDEX('%ID[0-9]%'. Затем я обрезаю строку, чтобы удалить часть идентификатора, а затем ищу первое нечисловое значение. Когда это будет сделано, вы можете использовать SUBSTRING между началом и первым нечисловым значением. Наконец, RIGHT(..., 4) получает последние четыре символа.
Конечно, этот код не сработает во многих некорректных строках, но их разрывы, входящие в данные своего рода «воздуходувки».
Это работает для меня! Большое спасибо, siggemannen, очень признателен :)
Хотя SQL не так дружелюбен, когда дело доходит до извлечения строк, вы можете попробовать следующее:
SELECT SUBSTRING([column], LEN([column]) - 4, 4)
FROM [table];
Этот запрос сначала определит длину строки в поле [столбец] с помощью функции LEN(). Затем он будет использовать функцию SUBSTRING() для извлечения последних 4 символов строки, начиная с длины строки минус 4. Вы также можете использовать следующий запрос для извлечения последних 4 цифр номера ID00000, даже если идентификационный номер различается по длине:
SELECT SUBSTRING([column], PATINDEX('%ID0%-%[0-9][0-9][0-9][0-9]%', [column]) + 6, 4)
FROM [table];
Этот запрос сначала будет использовать функцию PATINDEX(), чтобы найти позицию первого вхождения строки ID0%- в поле [столбец]. Затем он будет использовать функцию SUBSTRING() для извлечения последних 4 символов строки, начиная с позиции строки ID0% плюс 6.
Не существует решения, которое гарантировало бы вам правильный результат, потому что вы нарушаете 1FN, что является самым злым поступком...
Просто посмотрите, есть ли у вас новая строка, подобная этой:
insert into Sample (SampleString)
values ('Brian Larry, LUCID007700880099 Hays Hill XX, ID007370, Option code - 0123 4567, AAA - XX')
Или этот:
insert into Sample (SampleString)
values ('Brian LUCID0AZ, Hays Hill XX, ID007370, Option code - 0123 4567, AAA - XX')
SQL Server не ваш «друг» для этой задачи; манипулирование строками не является его сильной стороной. Тот факт, что у вас противоречивые форматы, разные разделители, противоречивые разделители (я смотрю на вас, Гэри) и разная длина идентификаторов, только усугубляет ситуацию. На самом деле вам следует нормализовать данные еще до того, как они попадут на ваш уровень SQL.