Блог

У меня есть столбец в таблице базы данных (SQL Server 2005), содержащий такие данные:

 TQ7394
SZ910284
T r1534
su8472
 

Я хотел бы обновить эту колонку, чтобы первые два символа были прописными. Я также хотел бы удалить любые пробелы между первыми двумя символами. Так T q1234 бы и стало TQ1234 .

Решение должно быть в состоянии справиться с несколькими пробелами между первыми двумя символами.

Возможно ли это в T-SQL? Как насчет ANSI-92? Мне всегда интересно посмотреть, как это делается и в других БД, поэтому не стесняйтесь публиковать ответы для PostgreSQL, MySQL и др.

Вот решение:

ИЗМЕНИТЬ: Обновлено для поддержки замены нескольких пробелов между первым и вторым символами, не являющимися пробелами

 /* TEST TABLE */
DECLARE @T AS TABLE(code Varchar(20))
INSERT INTO @T SELECT 'ab1234x1'   UNION SELECT ' ab1234x2' 
         UNION SELECT '  ab1234x3' UNION SELECT 'a b1234x4' 
         UNION SELECT 'a  b1234x5' UNION SELECT 'a   b1234x6' 
         UNION SELECT 'ab 1234x7'  UNION SELECT 'ab  1234x8' 

SELECT * FROM @T
/* INPUT
    code
    --------------------
      ab1234x3
     ab1234x2
    a   b1234x6
    a  b1234x5
    a b1234x4
    ab  1234x8
    ab 1234x7
    ab1234x1
*/

/* START PROCESSING SECTION */
DECLARE @s Varchar(20)
DECLARE @firstChar INT
DECLARE @secondChar INT

UPDATE @T SET
     @firstChar = PATINDEX('%[^ ]%',code)
    ,@secondChar = @firstChar   PATINDEX('%[^ ]%',  STUFF(code,1, @firstChar,'' ) )
    ,@s = STUFF(
            code,
            1,
            @secondChar,
            REPLACE(LEFT(code,
                    @secondChar
                ),' ','')
        ) 
     ,@s = STUFF(
            @s, 
            1,
            2,
            UPPER(LEFT(@s,2))
        )
    ,code = @s
/* END PROCESSING SECTION */

SELECT * FROM @T
/* OUTPUT
    code
    --------------------
    AB1234x3
    AB1234x2
    AB1234x6
    AB1234x5
    AB1234x4
    AB  1234x8
    AB 1234x7
    AB1234x1
*/
 

 UPDATE YourTable 
SET YourColumn = UPPER(
                   SUBSTRING(
                     REPLACE(YourColumn, ' ', ''), 1, 2
                   )
                 ) 
                   
                 SUBSTRING(YourColumn, 3, LEN(YourColumn))
 

UPPER это не повредит никаким цифрам, поэтому, если приведенные вами примеры полностью репрезентативны, на самом деле в этом нет никакого вреда:

 UPDATE tbl
SET col = REPLACE(UPPER(col), ' ', '')
 

В образцах данных в начале есть только пробелы и строчные буквы. Если это справедливо для реальных данных, то просто:

 UPPER(REPLACE(YourColumn, ' ', '')) 
 

Для более конкретного ответа я бы вежливо попросил вас расширить вашу спецификацию, иначе мне пришлось бы кодировать все другие возможности (например, значения менее трех символов), не зная, переоценивал ли я свое решение для обработки данных, которые на самом деле не возникли бы в реальности 🙂

Как всегда, как только вы исправите данные, установите ограничение базы данных, чтобы гарантировать, что плохие данные не повторятся, например

   ALTER TABLE YourTable ADD
     CONSTRAINT YourColumn__char_pos_1_uppercase_letter
        CHECK (ASCII(SUBSTRING(YourColumn, 1, 1)) BETWEEN ASCII('A') AND ASCII('Z'));

  ALTER TABLE YourTable ADD
     CONSTRAINT YourColumn__char_pos_2_uppercase_letter
        CHECK (ASCII(SUBSTRING(YourColumn, 2, 1)) BETWEEN ASCII('A') AND ASCII('Z'));
 

@huo73: ваш не работает для меня в SQL Server 2008: я получаю «TRr1534» вместо «TR1534».

 update Table set Column = case when len(rtrim(substring (Column , 1 , 2))) < 2 
            then UPPER(substring (Column , 1 , 1)   substring (Column , 3 , 1))   substring(Column , 4, len(Column)
            else UPPER(substring (Column , 1 , 2))   substring(Column , 3, len(Column) end
 

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

Если вы выполняете ОБНОВЛЕНИЕ, я бы сделал это в 2 этапа; сначала избавьтесь от пробела (RTRIM на ПОДСТРОКЕ), а во-вторых, сделайте ВЕРХНИЙ на первых 2 символах:

 // uses a fixed column length - 20-odd in this case
UPDATE FOO
SET bar = RTRIM(SUBSTRING(bar, 1, 2))   SUBSTRING(bar, 3, 20)

UPDATE FOO
SET bar = UPPER(SUBSTRING(bar, 1, 2))   SUBSTRING(bar, 3, 20)
 

Если вам это нужно в SELECT (т. Е. встроенном), то у меня возникнет соблазн написать скалярный UDF