Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*
- Splits string into parts delimitered with specified character.
- */
- CREATE FUNCTION [dbo].[SDF_SplitString]
- (
- @sString nvarchar(2048),
- @cDelimiter nchar(1)
- )
- RETURNS @tParts TABLE ( part nvarchar(2048) )
- AS
- BEGIN
- if @sString is null return
- declare @iStart int,
- @iPos int
- if substring( @sString, 1, 1 ) = @cDelimiter
- begin
- set @iStart = 2
- insert into @tParts
- values( null )
- end
- else
- set @iStart = 1
- while 1=1
- begin
- set @iPos = charindex( @cDelimiter, @sString, @iStart )
- if @iPos = 0
- set @iPos = len( @sString )+1
- if @iPos - @iStart > 0
- insert into @tParts
- values ( substring( @sString, @iStart, @iPos-@iStart ))
- else
- insert into @tParts
- values( null )
- set @iStart = @iPos+1
- if @iStart > len( @sString )
- break
- end
- RETURN
- END
- DECLARE @xml xml, @str varchar(100), @delimiter varchar(10)
- SET @str = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15'
- SET @delimiter = ','
- SET @xml = cast(('<X>'+replace(@str, @delimiter, '</X><X>')+'</X>') as xml)
- SELECT C.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as X(C)
- DECLARE @str varchar(100), @delimiter varchar(10)
- SET @str = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15'
- SET @delimiter = ','
- ;WITH cte AS
- (
- SELECT 0 a, 1 b
- UNION ALL
- SELECT b, CHARINDEX(@delimiter, @str, b) + LEN(@delimiter)
- FROM CTE
- WHERE b > a
- )
- SELECT SUBSTRING(@str, a,
- CASE WHEN b > LEN(@delimiter)
- THEN b - a - LEN(@delimiter)
- ELSE LEN(@str) - a + 1 END) value
- FROM cte WHERE a > 0
- SELECT TRY_CAST(value AS INT)
- FROM STRING_SPLIT ('1,2,3,4,5,6,7,8,9,10,11,12,13,14,15', ',')
- CREATE FUNCTION dbo.[String.Split]
- (
- @Text VARCHAR(MAX),
- @Delimiter VARCHAR(100),
- @Index INT
- )
- RETURNS VARCHAR(MAX)
- AS BEGIN
- DECLARE @A TABLE (ID INT IDENTITY, V VARCHAR(MAX));
- DECLARE @R VARCHAR(MAX);
- WITH CTE AS
- (
- SELECT 0 A, 1 B
- UNION ALL
- SELECT B, CONVERT(INT,CHARINDEX(@Delimiter, @Text, B) + LEN(@Delimiter))
- FROM CTE
- WHERE B > A
- )
- INSERT @A(V)
- SELECT SUBSTRING(@Text,A,CASE WHEN B > LEN(@Delimiter) THEN B-A-LEN(@Delimiter) ELSE LEN(@Text) - A + 1 END) VALUE
- FROM CTE WHERE A >0
- SELECT @R
- = V
- FROM @A
- WHERE ID = @Index + 1
- RETURN @R
- END
- SELECT dbo.[String.Split]('121,2,3,0',',',1) -- gives '2'
- CREATE FUNCTION [dbo].[split](
- @delimited NVARCHAR(MAX),
- @delimiter NVARCHAR(100)
- ) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
- AS
- BEGIN
- DECLARE @xml XML
- SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'
- INSERT INTO @t(val)
- SELECT r.value('.','varchar(MAX)') as item
- FROM @xml.nodes('/t') as records(r)
- RETURN
- END
- select * from dbo.split('1,2,3,4,5,6,7,8,9,10,11,12,13,14,15',',')
- DECLARE
- @InputString NVARCHAR(MAX) = 'token1,token2,token3,token4,token5'
- , @delimiter varchar(10) = ','
- DECLARE @xml AS XML = CAST(('<X>'+REPLACE(@InputString,@delimiter ,'</X><X>')+'</X>') AS XML)
- SELECT C.value('.', 'varchar(10)') AS value
- FROM @xml.nodes('X') as X(C)
- create function FnSplitToTableInt
- (
- @param nvarchar(4000)
- )
- returns table as
- return
- with Numbers(Number) as
- (
- select 1
- union all
- select Number + 1 from Numbers where Number < 4000
- ),
- Found as
- (
- select
- Number as PosIdx,
- convert(int, ltrim(rtrim(convert(nvarchar(4000),
- substring(@param, Number,
- charindex(N',' collate Latin1_General_BIN,
- @param + N',', Number) - Number))))) as Value
- from
- Numbers
- where
- Number <= len(@param)
- and substring(N',' + @param, Number, 1) = N',' collate Latin1_General_BIN
- )
- select
- PosIdx,
- case when isnumeric(Value) = 1
- then convert(int, Value)
- else convert(int, null) end as Value
- from
- Found
- select * from FnSplitToTableInt
- (
- '9, 8, 7, 6, 5, 4, 3, 2, 1, 0, ' +
- '9, 8, 7, 6, 5, 4, 3, 2, 1, 0, ' +
- '9, 8, 7, 6, 5, 4, 3, 2, 1, 0, ' +
- '9, 8, 7, 6, 5, 4, 3, 2, 1, 0, ' +
- '9, 8, 7, 6, 5, 4, 3, 2, 1, 0'
- )
- option (maxrecursion 4000)
- Create function [dbo].[udf_split] (
- @ListString nvarchar(max),
- @Delimiter nvarchar(1000),
- @IncludeEmpty bit)
- Returns @ListTable TABLE (ID int, ListValue nvarchar(1000))
- AS
- BEGIN
- Declare @CurrentPosition int, @NextPosition int, @Item nvarchar(max), @ID int, @L int
- Select @ID = 1,
- @L = len(replace(@Delimiter,' ','^')),
- @ListString = @ListString + @Delimiter,
- @CurrentPosition = 1
- Select @NextPosition = Charindex(@Delimiter, @ListString, @CurrentPosition)
- While @NextPosition > 0 Begin
- Set @Item = LTRIM(RTRIM(SUBSTRING(@ListString, @CurrentPosition, @NextPosition-@CurrentPosition)))
- If @IncludeEmpty=1 or LEN(@Item)>0 Begin
- Insert Into @ListTable (ID, ListValue) Values (@ID, @Item)
- Set @ID = @ID+1
- End
- Set @CurrentPosition = @NextPosition+@L
- Set @NextPosition = Charindex(@Delimiter, @ListString, @CurrentPosition)
- End
- RETURN
- END
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- CREATE FUNCTION [dbo].[SplitIntoBigints]
- (@List varchar(MAX), @Splitter char)
- RETURNS TABLE
- AS
- RETURN
- (
- WITH SplittedXML AS(
- SELECT CAST('<v>' + REPLACE(@List, @Splitter, '</v><v>') + '</v>' AS XML) AS Splitted
- )
- SELECT x.v.value('.', 'bigint') AS Value
- FROM SplittedXML
- CROSS APPLY Splitted.nodes('//v') x(v)
- )
- GO
- CREATE Function [dbo].[CsvToInt] ( @Array varchar(4000))
- returns @IntTable table
- (IntValue int)
- AS
- begin
- declare @separator char(1)
- set @separator = ','
- declare @separator_position int
- declare @array_value varchar(4000)
- set @array = @array + ','
- while patindex('%,%' , @array) <> 0
- begin
- select @separator_position = patindex('%,%' , @array)
- select @array_value = left(@array, @separator_position - 1)
- Insert @IntTable
- Values (Cast(@array_value as int))
- select @array = stuff(@array, 1, @separator_position, '')
- end
- /* *Object: UserDefinedFunction [dbo].[Split] Script Date: 10/04/2013 18:18:38* */
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- ALTER FUNCTION [dbo].[Split]
- (@List varchar(8000),@SplitOn Nvarchar(5))
- RETURNS @RtnValue table
- (Id int identity(1,1),Value nvarchar(100))
- AS
- BEGIN
- Set @List = Replace(@List,'''','')
- While (Charindex(@SplitOn,@List)>0)
- Begin
- Insert Into @RtnValue (value)
- Select
- Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
- Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
- End
- Insert Into @RtnValue (Value)
- Select Value = ltrim(rtrim(@List))
- Return
- END
- go
- Select *
- From [Clv].[Split] ('1,2,3,3,3,3,',',')
- GO
- CREATE FUNCTION Split
- (
- @delimited nvarchar(max),
- @delimiter nvarchar(100)
- ) RETURNS @t TABLE
- (
- -- Id column can be commented out, not required for sql splitting string
- id int identity(1,1), -- I use this column for numbering splitted parts
- val nvarchar(max)
- )
- AS
- BEGIN
- declare @xml xml
- set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r></root>'
- insert into @t(val)
- select
- r.value('.','varchar(max)') as item
- from @xml.nodes('//root/r') as records(r)
- RETURN
- END
- GO
- Select * from dbo.Split(N'1,2,3,4,6',',')
- CREATE FUNCTION [dbo].[DelimitedSplit8K]
- (@pString VARCHAR(8000), @pDelimiter CHAR(1))
- RETURNS TABLE WITH SCHEMABINDING AS
- RETURN
- --===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
- -- enough to cover NVARCHAR(4000)
- WITH E1(N) AS (
- SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
- SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
- SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
- ), --10E+1 or 10 rows
- E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
- E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
- cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
- -- for both a performance gain and prevention of accidental "overruns"
- SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
- ),
- cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
- SELECT 1 UNION ALL
- SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
- ),
- cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
- SELECT s.N1,
- ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
- FROM cteStart s
- )
- --===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
- SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
- Item = SUBSTRING(@pString, l.N1, l.L1)
- FROM cteLen l
- ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement