I’m trying to show only part of a string in my SQL Server query. How can I display the first 8 characters of a name and add ‘…’ at the end?
For example, if I have a name like ‘john smith’, I want it to show up as ‘john smi…’.
Here’s what I’ve tried so far:
SELECT LEFT(person_name, 8) + '...'
FROM people
WHERE person_id = 42
Is this the right way to do it? Or is there a better method? I’m not sure if I should use LEFT or SUBSTRING. Any help would be great!
yo GrowingTree! ur LEFT approach works fine for fixed length. but what if names are shorter? u might wanna try this:
SELECT LEFT(person_name, 8) + CASE WHEN LEN(person_name) > 8 THEN ‘…’ ELSE ‘’ END
FROM people
WHERE person_id = 42
this way it only adds ‘…’ when needed. cool, right?
hey there! have u considered using STUFF function? it’s pretty neat for this kinda thing. something like:
STUFF(person_name, 9, LEN(person_name), ‘…’)
this replaces everything after 8th char with ‘…’. what do u think? might be worth a shot!
Your approach using LEFT is a solid start, but there’s room for improvement. Consider this alternative:
SELECT
CASE
WHEN LEN(person_name) > 8 THEN LEFT(person_name, 8) + ‘…’
ELSE person_name
END AS truncated_name
FROM people
WHERE person_id = 42
This method handles both long and short names elegantly. For names longer than 8 characters, it truncates and adds ellipsis. For shorter names, it leaves them intact. This approach ensures consistent formatting while preserving full information for shorter names.