Uppercase first two characters in a column in a db table

Question

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.

Answer 1

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

This works on the fact that if there is a space then the trim of that part of string would yield length less than 2 so we split the string in three and use upper on the 1st and 3rd char. In all other cases we can split the string in 2 parts and use upper to make the first two chars to upper case.

Answer 2

If you are doing an UPDATE, I would do it in 2 steps; first get rid of the space (RTRIM on a SUBSTRING), and second do the UPPER on the first 2 chars:

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

If you need it in a SELECT (i.e. inline), then I'd be tempted to write a scalar UDF

Answer 3

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

Answer 4

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

Answer 5

UPPER isn't going to hurt any numbers, so if the examples you gave are completely representative, there's not really any harm in doing:

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

Answer 6

The sample data only has spaces and lowercase letters at the start. If this holds true for the real data then simply:

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

For a more specific answer I'd politely ask you to expand on your spec, otherwise I'd have to code around all the other possibilities (e.g. values of less than three characters) without knowing if I was overengineering my solution to handle data that wouldn't actually arise in reality :)

As ever, once you've fixed the data, put in a database constraint to ensure the bad data does not reoccur e.g.

  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: yours doesn't work for me on SQL Server 2008: I get 'TRr1534' instead of 'TR1534'.