1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51
|
-- Substring the description at the optimal character
-- Unicode takes 2 ASCII characters when printing
DECLARE @char unichar, @count int, @current int, @i int, @lg int,
@nb_ascii_chars int, @word univarchar(100)
SELECT @current = 1, @count = COUNT(*)
FROM #work
WHILE @current <= @count
BEGIN -- For each word
SELECT @word = description, @lg = DATALENGTH(description)
FROM #work
WHERE cpt = @current
SELECT @i = 1, @nb_ascii_chars = 0
WHILE @i <= @lg
BEGIN
SELECT @char = SUBSTRING(@word, @i, @i)
IF ASCII(@char) IS NULL
SELECT @nb_ascii_chars = @nb_ascii_chars + 2
ELSE
SELECT @nb_ascii_chars = @nb_ascii_chars + 1
-- Max length of the field
-- 40 ASCII characters, either 20 unicode characters
IF @nb_ascii_chars = 40
BEGIN
UPDATE #work
SET description = CONVERT(univarchar(40), SUBSTRING(@word, 1, @i))
WHERE cpt = @current
BREAK
END
SELECT @i = @i + 1
END
IF @nb_ascii_chars < 40
UPDATE #work
SET description = CONVERT(univarchar(40), LEFT(@word + SPACE(40), 40 - @nb_ascii_chars))
WHERE cpt = @current
SELECT @current = @current + 1
END
-- Final layout
SELECT description
+ SPACE(3)
+ "---"
FROM #work |
Partager