Как мне изменить тип данных столбца в TSQL? В частности, когда тип данных - VARCHAR на INT?

ProgramBox

Как мне изменить тип данных столбца в TSQL? В частности, когда тип данных — VARCHAR на INT?

Post author:admin
Запись опубликована:15 марта, 2023
Post category:Вопросы по программированию

#tsql

Вопрос:

У меня есть таблица, в ней три столбца: school_name, school_number, total_Enrollments . Однако total_Enrollments был загружен как тип данных столбца VARCHAR. Из-за этого в столбце есть дополнительные пробелы и символы.

Сценарий: в школе A зарегистрировано 621 человек, но поле было загружено с дополнительным пробелом до 631_, подчеркивание представляет пустое поле, когда я пытаюсь подвести итог, этого не произойдет, потому что это VARCHAR, поэтому я привел его как INT, чтобы подвести итог (раскрытие информации: я не былтот, который спроектировал эту таблицу таким образом, поле должно было быть числовым или INT с самого начала), но это не удается.

Решение: я не могу перестроить эту таблицу, есть ли способ через T-SQL изменить тип данных и удалить странные символы и лишние пробелы, чтобы я мог использовать функцию SUM для подсчета столбца?

1. Числа не содержат разрывов строк. Вам нужны чистые данные, прежде чем вы сможете изменить тип данных.

2. Есть функция try_cast learn.microsoft.com/en-us/sql/t-sql/functions /… который пытается привести varchar и в случае сбоя возвращает не ошибку, а нулевое значение. Вы могли бы, конечно, обрезать learn.microsoft.com/en-us/sql/t-sql/functions /… столбец перед try_cast, чтобы сначала удалить пробелы в начале или в конце.

3. Предполагается, что ваш столбец должен содержать только int данные или он тоже может содержать numeric данные? Такое значение, как '1.0' , например, не может быть напрямую преобразовано в an int .

4. Обратите внимание, что trim имеет необязательный синтаксис для удаления символов в определенном наборе, например declare @WhiteSpace as VarChar(10) = Char( 9 ) Char( 10 ) Char( 13 ) Char( 32 ); ... Trim( @WhiteSpace from MyColumn ) ... , для удаления табуляции, перевода строк, возврата каретки и пробелов.

5. Добавьте новый столбец int с нулевым значением. Заполните его результатом try_cast . Затем вы можете выбрать, что делать со значениями, которые не удалось исправить или удалить. Когда все будет готово, удалите старый столбец и переименуйте новое старое имя столбца в старое. Примечание: будьте осторожны с вещами, которые могут зависеть от старого столбца.

Ответ №1:

1) Если вы можете убедиться, что ваш требуемый номер находится в самой левой части строки, и у вас есть несколько нечисловых символов, вы можете использовать это:

 DROP TABLE IF EXISTS #source;
CREATE TABLE #source(total_Enrollments VARCHAR(100));
INSERT INTO #source VALUES ('101'), ('102_'), ('103 ');
INSERT INTO #source VALUES ('200'   CHAR(9)), ('201'   CHAR(10)), ('202'   CHAR(13));
INSERT INTO #source VALUES (CHAR(9)   '300'), (CHAR(10)   '301'), (CHAR(13)   '303');

SELECT
    total_Enrollments =
    CASE WHEN PATINDEX('%[^0-9]%', total_Enrollments) = 0
         THEN CAST(total_Enrollments AS INT)
         ELSE CAST(LEFT(total_Enrollments, PATINDEX('%[^0-9]%', total_Enrollments ) - 1) AS INT) END
  , pos  = PATINDEX('%[^0-9]%', total_Enrollments)
  , orig = total_Enrollments
FROM #source

PATINDEX(‘%[^0-9]%’) используйте регулярное выражение, где %[^0-9]% найдите первую позицию любого символа, который НЕ находится (выражается через ^) в диапазоне от 0 до 9. К сожалению, функция REPLACE не может обрабатывать регулярное выражение для замены нечислового символа на несколькопозиции.

2) Если вам нужно обработать CHAR(0), вы можете сделать это, заменив его на REPLACE-Function , ниже это сделано в подвыборке.

 DROP TABLE IF EXISTS #source;
CREATE TABLE #source(total_Enrollments VARCHAR(100));
INSERT INTO #source VALUES (CHAR(0)   '400'   CHAR(9));

SELECT
    total_Enrollments =
    CASE WHEN PATINDEX('%[^0-9]%', total_Enrollments) = 0
         THEN CAST(total_Enrollments AS INT)
         ELSE CAST(LEFT(total_Enrollments, PATINDEX('%[^0-9]%', total_Enrollments ) - 1) AS INT) END
  , pos  = PATINDEX('%[^0-9]%', total_Enrollments)
  , orig = total_Enrollments
FROM (SELECT total_Enrollments = REPLACE(total_Enrollments , CHAR(0), '') FROM #source) AS sub

Пожалуйста, обратите внимание, что REPLACE найдет и заменит CHAR(10), только если он встречается в начале строки. Если это происходит в любой другой позиции, вы должны использовать другую сортировку

ВЫБЕРИТЕ total_Enrollments = REPLACE(total_Enrollments СОПОСТАВЛЯЕТ SQL_Latin1_General_CP1_CI_AS, CHAR(0), «) ИЗ #source

1. Я не могу отблагодарить вас за 1, научив меня чему-то новому, и 2 за абсолютное исправление этого столбца в моей таблице. Это сработало отлично, и я хотел бы пожертвовать вам деньги за ваши усилия, чтобы найти время, чтобы помочь совершенно незнакомому человеку, просто знайте, что вы только что спасли нас от довольно большой задержки, и я просто не могу вас отблагодарить.

Ответ №2:

Вы должны попробовать обновить таблицу

 UPDATE <your_table>
SET total_Enrollments = REPLACE(total_Enrollments , CHAR(0), '')
WHERE CHARINDEX(CHAR(0), total_Enrollments ) > 0;

Но тогда вам также нужно будет исправить все, что помещает эти неверные данные в таблицу

 SELECT CONVERT(INT, REPLACE(total_Enrollments , CHAR(0), ''))
FROM <your_table>;