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

8,280 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

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

danb


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

Nice.

Thanks for sharing Tim. This will be very useful.


Landsystems Ltd ... Know your land | www.landsystems.co.nz

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