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 ......

If you only want to return a few characters of your long string, you can use:

select 
  left(col, 15) + '...' col
from yourtable

See SQL Fiddle with Demo.

This will return the first 15 characters of the string and then concatenates the ... to the end of it.

If you want to to make sure than strings less than 15 do not get the ... then you can use:

select 
  case 
    when len(col)>15
    then left(col, 15) + '...' 
    else col end col
from yourtable

See SQL Fiddle with Demo

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

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

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