I want to display after 8 characters, how to do that through substring.
For contact name is amjad habib, I want to display amjad hab....
SELECT SUBSTRING(contractname, 1, 8)
from contracts
where contractid = 613
1 Answer
This will cut your strings to a max length you can specify. The dots are only appended in case the string is to long and therefore truncated. Shorter strings are shown "as is":
DECLARE @tbl TABLE(TheName VARCHAR(100));
INSERT INTO @tbl VALUES('short'),('exactly8'),('something longer');
DECLARE @MaxLength INT=8;
SELECT TheName
,LEFT(TheName,@MaxLength) + CASE WHEN LEN(TheName)>@MaxLength THEN '...' ELSE '' END AS CutToMaxLength
FROM @tbl
The result
TheName CutToMaxLength
short short
exactly8 exactly8
something longer somethin...
answered Mar 7, 2016 at 12:22
Gottfried Lesigang
67.6k9 gold badges60 silver badges124 bronze badges
2 Comments
steenbergh
Pet peeve: when the truncated string + dots is longer than the string untrunc'ed. I would use 2 variables instead of
@MaxLength, i.e. @CutOffAt = 11 and @CutOffTo = 8Gottfried Lesigang
@steenbergh, If you need a dedicated max length including the dots, I'd define a variable
@truncSign with ... in it and then use it's LEN(@truncSign) in the calculation... But this is rather sophisticated ;-)
substring(contractname, 9, len(contractname))