Truncate each word in a string with (T-)SQL

Question

How can I 'truncate' each word in a phrase in SQL after a fixed number of chars?

For example: I want this data retrieved by the query

select descrizione 
from mytable

so are all the in same column.

FISIOPATOLOGIA DELLA RIPRODUZIONE UMANA
ODONTOIATRIA E STOMATOLOGIA
CHIRURGIA MAXILLO FACCIALE

Transformed like this (truncated if longer then 6 chars ), still in a single column:

FISIOL. DELLA RIPROD. UMANA
ODONTO. E STOMAT.
CHIRUR. MAXILL. FACCIA.

Which version of SQL Server are you using? If you're using SQL Server 2016 you could do this pretty easily starting with `STRING_SPLIT()`. — squillman, Aug 30 '18 at 14:09
More information required. What is the "truncate" rules? Some of those words aren't truncated. There's no obvious pattern of number of characters desired. — HardCode, Aug 30 '18 at 14:09
Why all the downvotes? This is actually a good and clear question. — Heinzi, Aug 30 '18 at 14:11
@DDS is your sample data one entire string, or are they separate rows in a table? If all one string, are there newlines in the string? — squillman, Aug 30 '18 at 14:13
@HardCode The rule was clarely written: take the first 6 characters of each word, if word was longer then 6 chars add a dot at the end — DDS, Aug 30 '18 at 14:13
I haven't downvoted yet, but I don't see any research effort in the question so far. This is a simple matter of splitting the string into words, using LEFT on each word, and rebuilding the string. — Tab Alleman, Aug 30 '18 at 14:15
@DDS What defines a "word" here? Is a space the only separator, or can there be punctuation marks as well (dots, commas etc')? — Zohar Peled, Aug 30 '18 at 14:16
@ZoharPeled words are space separated (apostrophes may appear) — DDS, Aug 30 '18 at 14:17

score 8 · Accepted Answer · answered Aug 30 '18 at 14:19

8

This makes use of DelimitedSplit8k, and thus relies you don't have a value of longer than 8000. Why not STRING_SPLIT? it's still missing that ordinal position column, vital for keeping results in the same order. Anyway, I think this get's you what you are after:

WITH VTE AS(
    SELECT *
    FROM (VALUES ('FISIOPATOLOGIA DELLA RIPRODUZIONE UMANA'),
                 ('ODONTOIATRIA E STOMATOLOGIA'),
                 ('CHIRURGIA MAXILLO FACCIALE')) V(String))
SELECT STUFF((SELECT ' ' + LEFT(DS.Item,6) + CASE WHEN LEN(DS.Item) > 6 THEN '.' ELSE '' END
              FROM dbo.DelimitedSplit8K(V.String,' ') DS
              ORDER BY DS.ItemNumber
              FOR XML PATH('')),1,1,'') AS ShortString
FROM VTE V;

answered Aug 30 '18 at 14:19

Thom A

88,727
11
45
75

2

+1000. You beat me posting nearly the same solution but yours is cleaner and simpler than mine. :) So silly that the STRING_SPLIT missed that critical component of ordinal position. Not sure why so many other splitters miss that either. Well done!! – Sean Lange Aug 30 '18 at 14:22
Thanks @SeanLange . it's a massive bug bear of mine that ordinal position is missing. I'm sure I've seen a connect item (or whatever it is MS use now) about adding it, but I doubt it'll be in the next version. – Thom A Aug 30 '18 at 14:24
Same for me. So often when splitting you need to reassemble or display in the correct order. – Sean Lange Aug 30 '18 at 14:25
Nice. I'd thought about the ordinal positioning issue shortly after commenting above but didn't go through the exercise of addressing it... – squillman Aug 30 '18 at 14:26
2

I'll admit, `STRING_SPLIT` is "pretty" reliable if you use something *silly* like `ORDER BY ROW_NUMBER() OVER (ORDER BY(SELECT NULL))` but do I ***want*** to trust SQL Server/the function to return it in the order it was given? I'd rather use a "safe" (and known) option over hopes and dreams. :) – Thom A Aug 30 '18 at 14:27
@DDS you're welcome. If it solved the issue, then please do mark it as the solution so that other's searching with a similar question can see that it helped you, and might be worth trying. Thanks. – Thom A Aug 30 '18 at 14:53
1

@Larnu SQL-Server introduced `STRING_SPLIT` with v2016. And it introduced `OPENJSON`. There is a great, [fast and *order-safe* splitting approach](https://stackoverflow.com/a/51401270/5089204). For a fast test you can check this `SELECT * FROM OPENJSON('[3,1,5]'); SELECT * FROM OPENJSON('["a","b","c"]');` It's fairly easy to transform the splitstring to a JSON-array. – Shnugo Aug 30 '18 at 20:31
1

@SeanLange my comment to Larnu might be useful for you too. – Shnugo Aug 30 '18 at 20:31

score 1 · Answer 2 · answered Aug 30 '18 at 20:47

1

This solution needs SQL-Server 2016 (for OPENJSON), but it will not need any function:

DECLARE @tbl TABLE(ID INT IDENTITY, YourText VARCHAR(100));
INSERT INTO @tbl VALUES
 ('FISIOPATOLOGIA DELLA RIPRODUZIONE UMANA')
,('ODONTOIATRIA E STOMATOLOGIA')
,('CHIRURGIA MAXILLO FACCIALE');

DECLARE @maxLength INT = 6;

SELECT t1.ID
      ,t1.YourText
      ,STUFF((
        SELECT ' ' + CASE WHEN LEN(A.[value])<=6 THEN A.[value] ELSE LEFT(A.[value],@maxLength) + '.' END
        FROM OPENJSON('["' + REPLACE(t1.YourText,' ','","') + '"]') A
        ORDER BY A.[key]
        FOR XML PATH(''),TYPE
       ).value('text()[1]','nvarchar(max)'),1,1,'')
FROM @tbl t1

OPENJSON returns the ordinal position in the column [key] when parsing an array.

answered Aug 30 '18 at 20:47

Shnugo

66,100
9
53
114

Nice. This looks pretty intriguing. I don't have a 2016+ instance at the moment but I will stand one up soon to take a look at this. Thanks for sharing!! – Sean Lange Aug 30 '18 at 20:54
Like Sean, I don't have access to SQL Server 2016, so I've not had the chance to learn about the JSON syntax in practice. `STRING_SPLIT` uses the same as `DelimitedSplit8K`, so easy to remember. :), I've seen this solution before though, if certainly is a great option, especially if someone has more than 8000/4000 characters in their `varchar`/`nvarchar`. – Thom A Aug 31 '18 at 07:59

Truncate each word in a string with (T-)SQL

2 Answers2