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 ......
-
3your "desired string" contains 28 characters from the "original string", not close to the "10 or 15" you are asking forKM.– KM.2013-02-28 20:10:35 +00:00Commented Feb 28, 2013 at 20:10
-
2KM instead of answering the question, you are making irrelevant comments. Everyone can understand what was being asked, but you are passing time by commenting on the length he gave in his exampleYogesh Thapliyal– Yogesh Thapliyal2023-12-07 21:13:10 +00:00Commented Dec 7, 2023 at 21:13
Add a comment
|
6 Answers
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
3 Comments
KM.
if the original string is less then 15 characters, you still get the
... appended when it does not apply
Taryn
@KM. added a version that will check for string length
Murphybro2
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'.
You can use
LEFT(column, length)
or
SUBSTRING(column, start index, length)
2 Comments
snaplemouton
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 msdn.microsoft.com/en-us/library/ms181984.aspx
pablete
Plus, the MSDN does not phrase the description of either function using the word TRUNCATE
You can also use the Cast() operation :
Declare @name varchar(100);
set @name='....';
Select Cast(@name as varchar(10)) as new_name
1 Comment
BillDarcy
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.
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.
Example
SELECT STUFF('12345', 2, 2, '')
Out: '145'
Comments
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
Comments
CASE
WHEN col IS NULL
THEN ''
ELSE SUBSTRING(col,1,15)+ '...'
END AS Col
1 Comment
questionto42
Even though it is the same as the other answer stackoverflow.com/a/15142838/11154841, this shows an example and also the "..." that the question asks for.