TVF_SplitStrings

The list of table valued functions used by Guildford College Reports and any anyone else wishes to share
Post Reply [phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable
User avatar
robinwilson16
Site Admin
Posts: 185
Joined: Sun Oct 16, 2016 6:46 pm
Forename: Robin
Surname: Wilson
College Name: FEA
Position: MIS Consultant
LinkedIn Profile: https://www.linkedin.com/in/robinwilson16
[phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable

TVF_SplitStrings

Post by robinwilson16 » Thu Mar 02, 2017 11:52 pm

Used to split up a single string of data using a specific delimiter and returns this as multiple rows.
Used by the address function

Code: Select all

CREATE FUNCTION [dbo].[TVF_SplitStrings] (
    @List       NVARCHAR(MAX),
    @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
AS

RETURN
	SELECT 
		RowNumber = ROW_NUMBER() OVER (ORDER BY Number),
		Value = Item
	FROM (
		SELECT 
			Number, 
			Item = 
				LTRIM(
					RTRIM(
						SUBSTRING(@List, Number, CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number
					) 
				)
		)
		FROM (
			SELECT 
				ROW_NUMBER() OVER (ORDER BY s1.[object_id])
			FROM sys.all_objects AS s1 
			CROSS APPLY sys.all_objects
		) AS n(Number)
		WHERE 
			Number <= CONVERT(INT, LEN(@List))
			AND SUBSTRING(@Delimiter + @List, Number, 1) = @Delimiter
		) AS y

Post Reply [phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable
[phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable
[phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable

Who is online

Users browsing this forum: No registered users and 0 guests