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

回答1:

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

回答2:

You can use

LEFT(column, length)

or

SUBSTRING(column, start index, length)

回答3:

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.

回答4:

You can also use the Cast() operation :

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

回答5:

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

回答6:

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