/* ITEM-*		=	"ITEM-"		"*" */
/* ITEM*		=	"ITEM"		"*" */
/* ITEM-????	=	"ITEM-"		"????" */
/* ITEM?????	=	"ITEM"		"????" */

/* * */

/* *.*		=	"*"	"."	"*" */
/* ??-*		=	"??"	"-"	"*" */
/* AB??-??cd	=	"AB"	"??"	"-"	"??"	"cd" */

/* 123456789 */
/* AB??-*DEF */



CREATE OR ALTER PROCEDURE MIG_GENERATE_MASK_SEGEMENTS_Sp (	@MASK_ID NVARCHAR(15),
													@string_value NVARCHAR(MAX) )
AS
BEGIN

	DECLARE @cur_char CHAR(1),
		@IS_SEQ_OR_CONST BIT,  -- 1 SEQ, 0 CONST
		@len INT,
		@str_len INT,
		@part_string NVARCHAR(MAX)

		DECLARE @result_set TABLE(MASK_ID NVARCHAR(15), SEGMENT NVARCHAR(MAX), IS_SEQ_OR_CONST BIT) 
		
		DECLARE @STR_TO_CHARS TABLE(INDX INT, CHAR_VAL NCHAR(1)) 

		--SET @MASK_ID = 'ITEM-1'
		--SET @string_value =  'AB??-??cd' -- 'ITEM-????' --
		SET @IS_SEQ_OR_CONST = 0
		SET @len = 0
		SET @str_len = LEN(@string_value)
		SET @part_string = NULL

		INSERT INTO @STR_TO_CHARS
		SELECT * FROM [MIG_STRING_TO_CHARS_Fn](@string_value)

		WHILE @len < @str_len 
		BEGIN
			
			SET @len = @len +1
			SELECT @cur_char = CHAR_VAL FROM @STR_TO_CHARS WHERE INDX = @len

			IF @IS_SEQ_OR_CONST = 0   -- SEQ
			BEGIN
				IF (@cur_char <> '?' AND @cur_char <> '*')
					BEGIN
						SET @part_string = CONCAT(@part_string, @cur_char)
						CONTINUE;
					END
				ELSE
					BEGIN
						IF(LEN(@part_string) > 0)
						BEGIN	
								INSERT INTO @result_set (MASK_ID, SEGMENT, IS_SEQ_OR_CONST) 
								VALUES(@MASK_ID, @part_string, @IS_SEQ_OR_CONST)
						END
					
						SET @part_string = NULL	
						SET @IS_SEQ_OR_CONST = 1
						SET @len = @len - 1

						CONTINUE;
					END
			END
	
			ELSE IF @IS_SEQ_OR_CONST = 1  -- CONST 
			BEGIN
				IF (@cur_char = '?' OR @cur_char = '*')
					BEGIN
						SET @part_string = CONCAT(@part_string, @cur_char)
						CONTINUE;
					END
				ELSE
					BEGIN
						IF(LEN(@part_string) > 0)
						BEGIN	
								INSERT INTO @result_set (MASK_ID, SEGMENT, IS_SEQ_OR_CONST) 
								VALUES(@MASK_ID, @part_string, @IS_SEQ_OR_CONST)
						END
					
						SET @part_string = NULL	
						SET @IS_SEQ_OR_CONST = 0
						SET @len = @len - 1

						CONTINUE;
					END
			END

		
		END

		-- To INSERT LAST OCCURANCE PART STRING
		IF(LEN(@part_string) > 0)
				BEGIN	
						INSERT INTO @result_set (MASK_ID, SEGMENT, IS_SEQ_OR_CONST) 
						VALUES(@MASK_ID, @part_string, @IS_SEQ_OR_CONST)
				END

		SELECT * FROM @result_set

END

