Skip to main content

MIGRATE SYN_CHARACTER_SORT function for HANA

Syniti Knowledge PlatformMigrate

ADMM Construct comes with a TSQL function, CONSTRUCT.dbo.

SYN_CHARACTER_SORT. An equivalent function should be available in the MIGRATE database when using HANA for the working databases. See below for a working definition.

CREATE FUNCTION "MIGRATE"."SYN_CHARACTER_SORT" (IN STRING NVARCHAR(1000))
RETURNS WORD NVARCHAR(1000)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
BEGIN
      DECLARE STR NVARCHAR(1000) := LTRIM(:STRING);
 
      SELECT STRING_AGG(SUBSTRING(:STR, N.ELEMENT_NUMBER, 1),'' ORDER BY SUBSTRING(:STR, N.ELEMENT_NUMBER, 1)) INTO WORD
      FROM SERIES_GENERATE_INTEGER(1, 1, LENGTH(:STR)+1) N;
       
END;
Posted by tom.woodward ·
2 comments

Log in to comment and vote

Comments2

  • tom.woodward

    •

    Nov 11, 2025

    oops, I forgot to add a default in case the input is blank. See updated code below

    CREATE FUNCTION "MIGRATE"."SYN_CHARACTER_SORT" (IN STRING NVARCHAR(1000))
RETURNS WORD NVARCHAR(1000)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
BEGIN
      DECLARE STR NVARCHAR(1000) := LTRIM(:STRING);
 
      SELECT STRING_AGG(SUBSTRING(:STR, N.ELEMENT_NUMBER, 1),'' ORDER BY SUBSTRING(:STR, N.ELEMENT_NUMBER, 1)) INTO WORD DEFAULT ''
      FROM SERIES_GENERATE_INTEGER(1, 1, LENGTH(:STR)+1) N;
       
END;

  • John Sanders

    •

    Oct 24, 2025

    Thanks Tom. Will get this addressed.