MSSQL String Parçamala ve Belirli Bir Sıradaki Değeri Alma

24 Caxah Alsa 2022 Omer-UYGUN MSSQL Server

Merhabalar MSSQL de SPLIT_STRING fonksiyonu ile parçalanan verilerden belirli bir sıradaki değeri almak için aşağıdaki fonksiyonlar kullanılabilir. Fonksiyonların kullanım sonuçları aşağıdaki gibidir.

 

Select dbo.SPLIT_STRING_COMMA_WITH_INDEX('Omer,UYGUN,abc',1);
Select dbo.SPLIT_STRING_COMMA_WITH_INDEX('Omer,UYGUN,abc',3);
Select dbo.SPLIT_STRING_COMMA_WITH_INDEX('Omer,UYGUN,abc',6);

Select dbo.SPLIT_STRING_WITH_INDEX('Omer,UYGUN,abc',',',1);
Select dbo.SPLIT_STRING_WITH_INDEX('Omer#UYGUN#abc','#',1);

 

USE [DATABASE_NAME]
GO

/****** Object:  UserDefinedFunction [dbo].[SPLIT_STRING_COMMA_WITH_INDEX]    Script Date: 25.05.2022 14:05:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE FUNCTION [dbo].[SPLIT_STRING_COMMA_WITH_INDEX]
(
	@paramStringValue nvarchar(MAX),
	@paramitemIndex int
)
RETURNS nvarchar(250)
AS
BEGIN
	declare @returnValue nvarchar(250);
	declare @splitedValue nvarchar(250);
	declare @splitedIndex int;

	declare splitCurs cursor
	for (select ROW_NUMBER() OVER(ORDER BY (Select 1)) as [index], value from STRING_SPLIT(@paramStringValue,','))

	OPEN splitCurs
	Fetch Next From splitCurs into @splitedIndex,@splitedValue

	while @@FETCH_STATUS=0
		BEGIN
			
			IF @splitedIndex=@paramitemIndex
				BEGIN
					set @returnValue=@splitedValue;
				END

			Fetch Next From splitCurs into @splitedIndex,@splitedValue
		END

	CLose splitCurs
	
	RETURN @returnValue;

END
GO

 

USE [DATABASE_NAME]
GO

/****** Object:  UserDefinedFunction [dbo].[SPLIT_STRING_WITH_INDEX]    Script Date: 25.05.2022 14:10:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE FUNCTION [dbo].[SPLIT_STRING_WITH_INDEX]
(
	@paramStringValue nvarchar(MAX),
	@paramSplitValue nvarchar(100),
	@paramitemIndex int
)
RETURNS nvarchar(250)
AS
BEGIN
	declare @returnValue nvarchar(250);
	declare @splitedValue nvarchar(250);
	declare @splitedIndex int;

	declare splitCurs cursor
	for (select ROW_NUMBER() OVER(ORDER BY (Select 1)) as [index], value from STRING_SPLIT(@paramStringValue,@paramSplitValue))

	OPEN splitCurs
	Fetch Next From splitCurs into @splitedIndex,@splitedValue

	while @@FETCH_STATUS=0
		BEGIN
			
			IF @splitedIndex=@paramitemIndex
				BEGIN
					set @returnValue=@splitedValue;
				END

			Fetch Next From splitCurs into @splitedIndex,@splitedValue
		END

	CLose splitCurs
	
	RETURN @returnValue;

END
GO

 

 

Add comment

Loading