Uppercase first two characters in db table column

Question

I have a column in a database table (SQL Server 2005) that contains this data:

TQ7394
SZ910284
T r1534
su8472

I would like to update this column so that the first two characters are uppercase. I would also like to remove any spaces between the first two characters. So it T q1234will become TQ1234.

The solution should be able to deal with several spaces between the first two characters.

Is this possible in T-SQL? What about the ANSI-92? I'm always interested to know how this is done in other db, so feel free to post answers for PostgreSQL, MySQL, etc.

Answer 1

Here is the solution:

EDIT:

/* 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
*/

Answer 2

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

Answer 3

UPPER , , , :

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

Answer 4

. , :

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".

Answer 5

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- char. , .

Answer 6

, 2 ; (RTRIM SUBSTRING), UPPER :

// 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 ( inline), UDF