SQL - Converting char to exact length varchar

Question

I am trying to do something like this:

SELECT CAST(MYCHARCOL AS VARCHAR(LEN(MYCHARCOL))) FROM MYTABLE

How do I convert a char column to varchar that is the exact length of the data inside that char column?

Answer 1

You can't do that

Try this

SELECT CAST(RTRIM(MYCHARCOL) AS VARCHAR(8000)) FROM MYTABLE

Answer 2

My first reaction was why would one want to do this to themselves?

My second reaction was to think that this would be easy. Turns out setting of length doesn't support variables. While the following code may work, I suggest that you ask yourself why you are attempting this operation and start from there.

BEGIN/*Char X to Varchar X   ==========================================================================================================*/


  USE msdb

  SELECT
    t.name
   ,c.name
   ,typ.name
   ,c.max_length
  FROM
    sys.tables t
    JOIN sys.columns c
      ON t.object_id = c.object_id
    JOIN sys.types typ
      ON c.user_type_id = typ.user_type_id
  WHERE
    typ.name IN ( N'varchar', N'nvarchar', N'char', N'nchar' )


  DECLARE @max_len INTEGER ;
  SELECT
    @max_len = c.max_length
  FROM
    sys.tables t
    JOIN sys.columns c
      ON t.object_id = c.object_id
    JOIN sys.types typ
      ON c.user_type_id = typ.user_type_id
  WHERE
    typ.name IN ( N'varchar', N'nvarchar', N'char', N'nchar' )
    AND t.name = N'backupset'
    AND OBJECT_SCHEMA_NAME(t.object_id) = N'dbo'
    AND c.name = N'type'



  --attempt 1
  SELECT
    as_a_char = bs.type
   ,hardcoded = CAST(bs.type AS VARCHAR(1))
    --, code_specified = CAST ( bs.type AS VARCHAR ( @max_len ) ) --When uncommented, Msg 102, Level 15, State 1, Line 33 - Incorrect syntax near '@max_len'.
  FROM
    msdb.dbo.backupset bs


  --attempt 2
  DECLARE @SQL NVARCHAR(MAX)
  SET @SQL = N'
  --attempt 1
  SELECT 
    as_a_char = bs.type
    , hardcoded = CAST ( bs.type AS VARCHAR(1) )
    , code_specified = CAST ( bs.type AS VARCHAR ( {@max_len} ) ) 
  FROM msdb.dbo.backupset bs
'

  SET @sql = REPLACE(@SQL, '{@max_len}', @max_len) ;

  EXECUTE ( @sql )

END/*Char X to Varchar X     ==========================================================================================================*/