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

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!

try this:

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

Can't you look up the type information for the columns in the system tables?

If not then to determine whether or not a column is varchar or nvarchar this would do it.

create table #test
(
c varchar(50),
n nvarchar(50)
)

insert into #test values ('1,2,3,4    ',N'1,2,3,4,5      ')

SELECT
       CASE
              WHEN datalength(CAST(c AS nvarchar(MAX))) = datalength(c)
              THEN 'c is nvarchar'
              ELSE 'c is char'
       END,
       CASE
              WHEN datalength(CAST(n AS nvarchar(MAX))) = datalength(n)
              THEN 'n is nvarchar'
              ELSE 'n is char'
       END
FROM   #test