USE [xxxxxxxxxxxx]
GO
/****** Object:  UserDefinedFunction [dbo].[Gizle]    Script Date: 21.04.2021 12:04:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[Gizle]
(
    @Text NVARCHAR(MAX),
    @Ayrac CHAR(1),
    @Goster INT
)
RETURNS NVARCHAR(MAX)
AS
BEGIN

    --DECLARE @Text NVARCHAR(MAX)
    --DECLARE @Ayrac CHAR
    --DECLARE @Goster INT


    --SET @Text = 'Buraya yazacağınız her yazı parametrelerde verilen goster deki rakam kadar başından gösterilir diğerleri yıldız ile kaplanır. Uzunluk aynıdır.'
    --SET @Ayrac = ' '
    --SET @Goster = 3

	IF	@Text IS NULL
	BEGIN
	    SET @Text = 'Okunacak veri yok'
	END

    SET @Text = REPLACE(@Text, '-', '  ');
    SET @Text = REPLACE(@Text, '(', '');
    SET @Text = REPLACE(@Text, ') ', '');
    SET @Text = REPLACE(@Text, '/', '');
    SET @Text = REPLACE(@Text, '  ', '');
    SET @Text = REPLACE(@Text, '&', '  ');
    SET @Text = REPLACE(@Text, '+', '  ');
    SET @Text = REPLACE(@Text, '- ', '');
    SET @Text = REPLACE(@Text, ' -', '');
    SET @Text = REPLACE(@Text, ' )', '');
    SET @Text = REPLACE(@Text, '( ', '');
    SET @Text = REPLACE(@Text, ') ', '');
    SET @Text = REPLACE(@Text, ' (', '');



    DECLARE @Deger NVARCHAR(MAX);

    SET @Deger =
    (
        SELECT *
        FROM
        (
            SELECT SUBSTRING(
                   (
                       SELECT ' ' + SUBSTRING(Value, 0, @Goster + 1)
                              + SUBSTRING(
                                             '************************************************************************',
                                             0,
                                             LEN(a.Value) - 2
                                         ) AS 'data()'
                       FROM
                       (
                           SELECT LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'Value'
                           FROM
                           (
                               SELECT CAST('<M>' + REPLACE(@Text, @Ayrac, '</M><M>') + '</M>' AS XML) AS Data
                           ) AS A
                               CROSS APPLY Data.nodes('/M') AS Split(a)
                       ) AS a WHERE LEN(a.Value) > @Goster
                       FOR XML PATH('')
                   ),
                   2,
                   9999
                            ) AS B
        ) AS c
    );
    RETURN @Deger;
END;



--SELECT dbo.Gizle(CARI_ISIM, ' ', 3)
--FROM dbo.TBLCASABIT;