create FUNCTION [dbo]. [Splittotable] (@SplitString nvarchar (max), @Separator nvarchar (10) = ") RETURNS @SplitStringsTable TABLE ([id] int identity (), [value] nvarchar (max)) as BEGIN DECLARE @CurrentIndex int DECLARE @NextIndex int; DECLARE @ReturnText nvarchar (max); SELECT @CurrentIndex = 1; while (@CurrentIndex <=len (@SplitString)) BEGIN SELECT @NextIndex =charindex (@Separat Or, @SplitString, @CurrentIndex); IF (@NextIndex =0 OR @NextIndex is NULL) SELECT @NextIndex =len (@SplitString) +1; SELECT @ReturnText =substring (@SplitString, @CurrentIndex, @[email protected]); INSERT into @SplitStringsTable ([value]) VALUES (@ReturnText); SELECT @[email protected]+1; END RETURN; END
Use
DECLARE @TR NVARCHAR (50)
DECLARE @TRS NVARCHAR (1000)
SET @TRS = ' 123,456,789 '
Set @TR = (select top 1 VALUE from dbo. Splittotable (@TRS, ', ') Order by NEWID ()) --the outermost bracket must be added
PRINT (@TR)
SQL Server Split function