[sql-server] How to truncate string using SQL server

i have large string in SQL Server. I want to truncate that string to 10 or 15 character

Original string

this is test string. this is test string. this is test string. this is test string.

Desired string

this is test string. this is ......

     CASE
     WHEN col IS NULL
        THEN ''
     ELSE SUBSTRING(col,1,15)+ '...' 
     END AS Col

I think the answers here are great, but I would like to add a scenario.

Several times I've wanted to take a certain amount of characters off the front of a string, without worrying about it's length. There are several ways of doing this with RIGHT() and SUBSTRING(), but they all need to know the length of the string which can sometimes slow things down.

I've use the STUFF() function instead:

SET @Result = STUFF(@Result, 1, @LengthToRemove, '')

This replaces the length of unneeded string with an empty string.

You could also use the below, the iif avoids the case statement and only adds ellipses when required (only good in SQL Server 2012 and later) and the case statement is more ANSI compliant (but more verbose)

SELECT 
  col, LEN(col), 
  col2, LEN(col2), 
  col3, LEN(col3) FROM (
  SELECT 
    col, 
    LEFT(x.col, 15) + (IIF(len(x.col) > 15, '...', '')) AS col2, 
    LEFT(x.col, 15) + (CASE WHEN len(x.col) > 15 THEN '...' ELSE '' END) AS col3 
  from (
      select 'this is a long string. One that is longer than 15 characters' as col
      UNION 
      SELECT 'short string' AS col
      UNION 
      SELECT 'string==15 char' AS col
      UNION 
      SELECT NULL AS col
      UNION 
      SELECT '' AS col
) x
) y

You can use

LEFT(column, length)

or

SUBSTRING(column, start index, length)

You can also use the Cast() operation :

 Declare @name varchar(100);
set @name='....';
Select Cast(@name as varchar(10)) as new_name