Substring sql server

Question

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

`substring(contractname, 9, len(contractname))` – Gordon Linoff Mar 07 '16 at 12:12 — Gordon Linoff, Mar 07 '16 at 12:12
how to display dots after 8 characters? – Muhammad farhan Mar 07 '16 at 12:14 — Muhammad farhan, Mar 07 '16 at 12:14

score 4 · Answer 1 · answered Mar 07 '16 at 12:22

4

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 07 '16 at 12:22

Shnugo

66,100
9
53
114

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 = 8` – steenbergh Mar 07 '16 at 14:07
@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 ;-) – Shnugo Mar 07 '16 at 14:36

Substring sql server

1 Answers1