SQL-запрос: как преобразовать данные в числа, только если они не содержат букв
При извлечении ссылок на продукты ERP с сервера SQL они извлекаются как текст. Эти ссылки иногда содержат только цифры, а иногда содержат цифры + буквы CN. Я хотел бы извлечь его как число, если оно содержит только числа, и как текст, если оно содержит буквы CN. К сожалению, я не нашел способ сделать это.
Это то, что я пробовал ("INVMB.MB110" является полем "Ссылка" на сервере SQL):
SELECT
CASE
WHEN INVMB.MB110 LIKE '%CN%'
THEN INVMB.MB110
ELSE CAST(INVMB.MB110 AS INT) END
AS 'Reference'
Я ожидал преобразовать в целое число только в том случае, если ссылка не содержит «CN», но, похоже, она пытается преобразовать ее в любом случае. Вот сообщение об ошибке, которое я получаю:
Не удалось преобразовать значение varchar '30903CN013' в тип данных int.
«Я хотел бы извлечь его как число, если оно содержит только цифры, и как текст, если оно содержит буквы», что это значит? Столбец может иметь только один тип данных, какой вам нужен?
@jarlh, хорошо, спасибо за эту информацию.
@Charlieface, спасибо за эту информацию. Если мне нужно выбрать тип данных, мне понадобится целое число. Тем не менее, как это будет работать с данными, содержащими буквы?
Это не будет. Вы не можете хранить буквы и знаки препинания в числовом столбце. Вам нужно решить, хотите ли вы вернуть это. Вы могли бы просто вернуться NULL например SELECT TRY_CAST(INVMB.MB110 AS INT) AS Reference
Я думаю, вам нужно переосмыслить то, что вы на самом деле хотите. Почему для вас важен тип столбца? Вы не можете поместить и строку, и целое число в один и тот же столбец, если только это не строка, которая вернет вас туда, откуда вы начали. Планируете ли вы что-то делать с данными или каков следующий шаг вашей операции?
Ответы 1
Вы можете создать два столбца: один для строковых данных, а другой для числовых данных. Предполагая, что у вас есть SQL Server 2016 или выше, вы можете использовать функцию TRY_CONVERT или TRY_PARSE.
SELECT
[INVMB.MB110_INT] = TRY_CONVERT(INT, [INVMB.MB110])
,[INVMB.MB110_STRING] = [INVMB.MB110]
,[IsNumberDataType] = CASE WHEN TRY_CONVERT(INT, [INVMB.MB110]) IS NULL THEN 0 ELSE 1 END
Функция TRY_CONVERT преобразует строку в числовое значение, если это возможно, в противном случае она вернет NULL.
Обратите внимание, что функция TRY_CONVERT вернет числовое значение 0, если исходная строка является пустой строкой. Если вы не хотите, чтобы 0 был перенастроен на пустую строку, используйте функцию TRY_PARSE.
Если вам абсолютно необходимы значения в одном столбце, вы можете преобразовать значения в типы SQL_VARIANT (после преобразования строки в желаемый базовый тип). Я лично избегаю типов SQL_VARIANT как чумы, потому что с ними связано слишком много проблем с производительностью и неявным преобразованием. Я включил пример запроса, который возвращает некоторые образцы данных.
WITH VariantSampleData
AS
(
SELECT
variant_value = TRY_CONVERT(SQL_VARIANT, TRY_CONVERT(INT, '123') )
UNION ALL
SELECT
variant_value = TRY_CONVERT(SQL_VARIANT, 'abc' )
)
SELECT
variant_value
,BaseType = SQL_VARIANT_PROPERTY(variant_value,'BaseType')
,[math] = TRY_CONVERT(INT, variant_value) + 1
FROM
VariantSampleData
спасибо за Ваш ответ. Я предполагаю, что то, что я хотел сделать, невозможно сделать без нескольких столбцов, как вы предлагаете.
@Luc-OlivierPineau, вы можете использовать тип данных sql_variant для хранения разных типов данных в одном столбце, но для выполнения любой операции со значениями вам все равно нужно преобразовать вариант обратно в базовый тип данных. Существует также много накладных расходов при использовании sql_variant, и вы, вероятно, столкнетесь со всевозможными проблемами неявного преобразования при запросе ваших данных.
В выражениях case все значения THEN должны иметь совместимые типы данных. Это означает, что ваш
THEN INVMB.MB110неявно приводится к типу int.