SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP FUNCTION IF EXISTS [dbo].[ufn_ssERP8_StringToWord]
GO
CREATE FUNCTION [dbo].[ufn_ssERP8_StringToWord]
-- ===============================================================================================
-- SELECT * FROM [dbo].[ufn_ssERP8_StringToWord] (N'ала', null)
-- Author		Щерю Щерев
-- Create date	02/04/2025 
-- Description	Разделяне на стринг на думи
-- Returns		@Words ( Word nvarchar(255))
-- Parameters
	(	@String	nvarchar(1024), @Delimiters nvarchar(32)	)
-- ===============================================================================================
RETURNS @Words TABLE ( [Word] nvarchar(255))
AS BEGIN
/*
manualUpdateFlag: No
*/
	-- declare @String	nvarchar(1024) = N'ала бала<портокала', @Delimiters nvarchar(32); declare @Words TABLE ( [Word] nvarchar(255))
		DECLARE @ServiceDelimiter nchar(1) = N'■' -- ALT+254:declare @j int = 0; while @j<256 begin select @j,CHAR(@j); SET @j+=1 end
		SET @String = TRIM(@String)
		DECLARE @LenString int = LEN(@String)
	IF @LenString>2	BEGIN	-- поне 2 символа
	DECLARE @StringNew nvarchar(1024)
		IF LEFT(@String,1)=N'"' and RIGHT(@String,1)=N'"' -- пълно съвпадение
			SET @StringNew = SUBSTRING(@String,2,@LenString-2)
		ELSE	begin	-- търсене по думи
				DECLARE @DelimitersNew nvarchar(32)= case when @Delimiters is null then N'!"#$%&()*+=<,-.:; >?@[\]^_`{|}~‘’“”•–©™­®Є«­®±»¶°'''
														  --when @Delimiters is null then N'!"#$%&()*+=<,-.:; >?@[\]^_`{|}'
														  when CHARINDEX(@Delimiters, SPACE(1)) > 0 then @Delimiters
														  else SPACE(1)+@Delimiters
													end	
				DECLARE @LenDelimitersNew int = DATALENGTH(@DelimitersNew)/2
				--select N'>>'+@DelimitersNew+N'<<', @LenDelimitersNew,REPLICATE(@ServiceDelimiter, @LenDelimitersNew)
				SET @StringNew = TRANSLATE( @String, @DelimitersNew, REPLICATE(@ServiceDelimiter, @LenDelimitersNew))
				
				end
						END
	--select @StringNew
	INSERT INTO @Words SELECT * FROM string_split(@StringNew,@ServiceDelimiter)
	DELETE @Words WHERE NULLIF(TRIM([word]),N'') is null
	--select * from @Words
RETURN
END