Uppercase first two characters in a column in a db table

language-agnostic, sql, sql-server, t-sql

Solution

Here is a solution:

EDIT: Updated to support replacement of multiple spaces between the first and the second non-space characters

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

Problem

I've got a column in a database table (SQL Server 2005) that contains data like this: ``` 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 `T q1234` would become `TQ1234`. The solution should be able to cope with multiple spaces between the first two characters. Is this possible in T-SQL? How about in ANSI-92? I'm always interested in seeing how this is done in other db's too, so feel free to post answers for PostgreSQL, MySQL, et al.

Original source