Extracting partial text in SQL Server

Data and Databases
1

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!

2

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?

3

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!

4

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.