6u6FQNSh

1SELECT SUBSTRING('Business Analist met focus op wet- en regelgeving', 1, 28)
2	
3Business Analist met focus o
4	
5Business Analist met focus
6	
7SELECT
8  LEFT(str, cutpos) AS str1,
9  SUBSTRING(str, cutpos + 1) AS str2
10FROM (
11  SELECT
12    @str AS str,
13    @len - IFNULL(NULLIF(LOCATE(' ', REVERSE(LEFT(@str, @len))), 0) - 1, 0) AS cutpos
14) s
15	
16SELECT SUBSTRING_INDEX('Business Analist met focus op wet- en regelgeving',' ',4)
17	
18Business Analist met focus
19	
20//gets initial string - use 29 instead of 28 to see if the 29th  character is a space
21SELECT SUBSTRING('Business Analist met focus op wet- en regelgeving', 1, 29) 
22
23//inverts the string, so we can get the first 
24SELECT REVERSE( SUBSTRING('Business Analist met focus op wet- en regelgeving', 1, 29))
25
26// find the charindex of the first space (last space in the string not reversed)
27SELECT CHARINDEX(' ', REVERSE( SUBSTRING('Business Analist met focus op wet- en regelgeving', 1, 29)))
28
29// get the substring from the first (last) space
30SELECT  SUBSTRING(REVERSE( SUBSTRING('Business Analist met focus op wet- en regelgeving', 1, 29)), CHARINDEX(' ', REVERSE( SUBSTRING('Business Analist met focus op wet- en regelgeving', 1, 29))), 29)
31
32// reverse the string again to unfold it.
33SELECT REVERSE(SUBSTRING(REVERSE( SUBSTRING('Business Analist met focus op wet- en regelgeving', 1, 29)), CHARINDEX(' ', REVERSE( SUBSTRING('Business Analist met focus op wet- en regelgeving', 1, 29))), 29))
34
35
36// to try different lengths...
37DECLARE  @size  int
38select @size = 24
39SELECT REVERSE(SUBSTRING(REVERSE( SUBSTRING('Business Analist met focus op wet- en regelgeving', 1, @size)), 
40CHARINDEX(' ', REVERSE( SUBSTRING('Business Analist met focus op wet- en regelgeving', 1, @size))), @size))
41	
42select Substring('Business Analist met focus op wet- en regelgeving', 0 , 28 + 2 - CharIndex(' ',  REVERSE(SUBSTRING('Business Analist met focus op wet- en regelgeving', 0, 28 + 1 )),0))
43	
44IF (
45CHARACTER_LENGTH(
46  IF(
47    a.descr = '',
48    a.product,
49    CONCAT_WS(' - ',a.product,a.descr)
50  )
51)>35,
52IF(
53  a.descr = '',
54  CONCAT(
55    REVERSE(SUBSTRING(REVERSE( SUBSTRING(a.product, 1, 35)), locate(' ', REVERSE( SUBSTRING(a.product, 1, 35))), 35)),
56    '...'
57  ),
58  CONCAT(
59    REVERSE(SUBSTRING(REVERSE( SUBSTRING(CONCAT_WS(' - ',a.product,a.descr), 1, 35)), locate(' ', REVERSE( SUBSTRING(CONCAT_WS(' - ',a.product,a.descr), 1, 35))), 35)),
60    '...'
61  )
62),
63CONCAT_WS(' - ',a.product,a.descr)
64)
65	
66SELECT
67  IF(LENGTH(str)<=@len,str,LEFT(str, cutpos)) AS str1,
68  IF(LENGTH(str)<=@len,'',SUBSTRING(str, cutpos + 1)) AS str2
69FROM (
70  SELECT
71    @str AS str,
72    @len - IFNULL(NULLIF(LOCATE(' ', REVERSE(LEFT(@str, @len))), 0) - 1, 0) AS cutpos
73  FROM @table
74) s
75	
76CREATE TABLE `test` (
77  `sometext` varchar(65)
78);
79
80INSERT INTO `test` (`sometext`) VALUES
81('Firs strin'),
82('Alll right'),
83('third string'),
84('fourth string'),
85('a longer example string'),
86('Supercalifragilisticexpialidocious');
87
88SELECT
89  IF(LENGTH(str)<=12,str,LEFT(str, cutpos)) AS str1,
90  IF(LENGTH(str)<=12,'',SUBSTRING(str, cutpos + 1)) AS str2
91FROM (
92  SELECT
93    sometext AS str,
94    12 - IFNULL(NULLIF(LOCATE(' ', REVERSE(LEFT(sometext, 12))), 0) - 1, 0) AS cutpos
95  FROM test
96) s
97	
98SELECT
99  LEFT(str, cutpos) AS str1,
100  SUBSTRING(str, cutpos + 1) AS str2
101FROM (
102  SELECT
103    sometext AS str,
104    12 - IFNULL(NULLIF(LOCATE(' ', REVERSE(LEFT(sometext,12))), 0) - 1, 0) AS cutpos
105  FROM test
106) s
107	
108DROP FUNCTION IF EXISTS fn_maxlen;
109delimiter //
110CREATE FUNCTION fn_maxlen(s TEXT, maxlen INT) RETURNS VARCHAR(255)
111BEGIN
112
113 RETURN LEFT(s, maxlen - LOCATE(' ', REVERSE(LEFT(s, maxlen))));
114
115END//
116delimiter ;
117	
118SELECT fn_maxlen('Business Analist met focus op wet- en regelgeving', 28);