T-SQL: How to obtain the exact length of a string in characters?

Question

I'm generating T-SQL SELECT statements for tables for which I have no data type information up-front. In these statements, I need to perform string manipulation operations that depend on the length of the original value of the tables' columns.

One example (but not the only one) is to insert some text at a specific position in a string, including the option to insert it at the end:

SELECT 
  CASE WHEN (LEN ([t0].[Product] = 8) 
    THEN [t0].[Product] + 'test' 
    ELSE STUFF ([t0].[Product], 8, 0, 'test') 
  END
FROM [OrderItem] [t0]

(The CASE WHEN + LEN is required because STUFF doesn't allow me to insert text at the end of a string.)

The problem is that LEN excludes trailing blanks, which will ruin the calculation. I know I can use DATALENGTH, which does not exclude trailing blanks, but I can't convert the bytes returned by DATALENGTH to the characters required by STUFF because I don't know whether the Product column is of type varchar or nvarchar.

So, how can I generate a SQL statement that depends on the exact length of a string in characters without up-front information about the string data type being used?

Answer 1

Here's what I ended up using:

SELECT   
  CASE WHEN ((LEN ([t0].[Product] + '#') - 1) = 8)   
    THEN [t0].[Product] + 'test'   
    ELSE STUFF ([t0].[Product], 8, 0, 'test')   
  END  
FROM [OrderItem] [t0]  

Measurements indicate that the LEN (... + '#') - 1 trick is about the same speed as LEN (...) alone.

Thanks for all the good answers!