USE [xxxxxx];
GO
/****** Object:  UserDefinedFunction [dbo].[Gizle]    Script Date: 17.01.2024 15:46:16 ******/
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

CREATE FUNCTION [dbo].[_Brk_Yaz_MetniGizle]
(
    @Text NVARCHAR(MAX),
    @Ayrac CHAR(1),
    @Goster INT
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    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, ' (', '');
    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;


--Kullanim;
--SELECT [dbo].[_Brk_Yaz_MetniGizle](CARI_ISIM, ' ', 3)
--FROM dbo.TBLCASABIT;

----demo datalarınızda kullanabilirsiniz;
----UPDATE dbo.TBLCASABIT
----SET CARI_ISIM = [dbo].[_Brk_Yaz_MetniGizle](CARI_ISIM, ' ', 3)