Subscribe to this thread
Home - General / All posts - Get Nth token in string

8,743 post(s)
#19-Sep-18 01:52

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


FUNCTION StringTokenNth(

    @input NVARCHAR, @n INT32, @separator NVARCHAR, @trim BOOLEAN



    SELECT CASE @trim

        WHEN TRUE THEN StringTrim([Value], WHITESPACE)

        ELSE [Value]




        TABLE CALL StringToTokens(@input, @separator)


    OFFSET @n

    FETCH 1




? 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.)


1,697 post(s)
#19-Sep-18 09:24


Thanks for sharing Tim. This will be very useful.

Landsystems Ltd ... Know your land |

Manifold User Community Use Agreement Copyright (C) 2007-2017 Manifold Software Limited. All rights reserved.