This SQL9 function is similar to the built-in Token() function in Manifold 8 SQL, except: - the index is zero-based (in line with built-in string functions in SQL9)
- the separator must be a single character
- there is optional trimming of the result
- all arguments are required
--SQL9 FUNCTION StringTokenNth( @input NVARCHAR, @n INT32, @separator NVARCHAR, @trim BOOLEAN ) NVARCHAR AS ( SELECT CASE @trim WHEN TRUE THEN StringTrim([Value], WHITESPACE) ELSE [Value] END FROM ( TABLE CALL StringToTokens(@input, @separator) ) OFFSET @n FETCH 1 ) END; E.g. ? StringTokenNth('6B. Spit enclosed', 0, '.', FALSE) --> '6B' ? StringTokenNth('6B. Spit enclosed', 1, '.', FALSE) --> ' Spit enclosed' ? StringTokenNth('6B. Spit enclosed', 2, '.', TRUE) --> 'Spit enclosed' ? StringTokenNth('6B. Spit enclosed', 3, '.', FALSE) --> NULL (There may be a better way to do it.)
|