How to truncate string using SQL server

Question

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

your "desired string" contains 28 characters from the "original string", not close to the "10 or 15" you are asking for — KM., Feb 28 '13 at 20:10

score 215 · Accepted Answer · edited Oct 25 '21 at 19:31

215

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

edited Oct 25 '21 at 19:31

Richard Marskell - Drackir

13,080
3
33
54

answered Feb 28 '13 at 17:58

Taryn

242,637
56
362
405

2

if the original string is less then 15 characters, you still get the `...` appended when it does not apply – KM. Feb 28 '13 at 20:08
1

@KM. added a version that will check for string length – Taryn Feb 28 '13 at 20:12
3

If col is exactly 15 in length it will get the entire string when doing left(col,15) and then put '...' on the end. Surely a better solution is checking 'when len(col) > 15'. – Murphybro2 Dec 06 '18 at 11:10

score 49 · Answer 2 · edited Aug 02 '18 at 09:04

49

You can use

LEFT(column, length)

or

SUBSTRING(column, start index, length)

edited Aug 02 '18 at 09:04

marsze

15,079
5
45
61

answered Feb 28 '13 at 18:24

snaplemouton

1,459
14
28

1

I'm sorry if my answer sounded rude, it wasn't meant to be rude. But it still holds true that this question shouldn't have been asked in the first place as it is very easy to find an answer. MSDN is an amazing way to find answers and it is more then easy to find the said answer on it. You don't have to go on 500 pages to find how to truncate a string in SQL. You get all string function here http://msdn.microsoft.com/en-us/library/ms181984.aspx – snaplemouton Jan 08 '14 at 19:11
12

Plus, the MSDN does not phrase the description of either function using the word **TRUNCATE** – pablete Jan 14 '15 at 16:00

score 6 · Answer 3 · answered Mar 25 '15 at 09:14

6

You can also use the Cast() operation :

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

answered Mar 25 '15 at 09:14

goli55

61
1
2

1

I like this one best when outputting to a text file because only the new number of characters are allocated to that column in the output text file. (e.g. 50 instead of 1000) for more compact results. – BillDarcy Apr 15 '15 at 20:01

score 4 · Answer 4 · answered Feb 24 '15 at 23:38

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.

score 1 · Answer 5 · answered May 31 '17 at 21:11

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

score 0 · Answer 6 · answered Jul 03 '17 at 13:19

0

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

answered Jul 03 '17 at 13:19

UpwardD

739
4
12
36

How to truncate string using SQL server

6 Answers6

Linked

Related