TSQL SplitInts, SplitStrings: string to list of string and to list of ints

splitstrings.sql

CREATE FUNCTION SplitInts
  (
    @list      varchar(8000),
    @delimiter char(1) = '#'
  )
RETURNS TABLE WITH SCHEMABINDING
AS
  RETURN (
    SELECT [value] from (
      SELECT [value] = cast(y.i.value('(./text())[1]', 'varchar(8000)') as int)
        FROM (SELECT x = CONVERT(XML, '<i>'
                  + REPLACE(@list, @delimiter, '</i><i>')
                  + '</i>').query('.')) AS a
        CROSS APPLY x.nodes('i') AS y (i)
    ) v where coalesce(@list,'') != ''
  )
GO;
-- select * from utils.SplitInts ('11,44,77,99', ',')
      
      
CREATE FUNCTION SplitStrings
  (
    @list      varchar(8000),
    @delimiter char(1) = '#'
  )
RETURNS TABLE WITH SCHEMABINDING
AS
  RETURN (
    SELECT [value] from (
      SELECT [value] = coalesce(y.i.value('(./text())[1]', 'varchar(8000)'),'')
        FROM (SELECT x = CONVERT(XML, '<i>'
                  + REPLACE(@list, @delimiter, '</i><i>')
                  + '</i>').query('.')) AS a
        CROSS APPLY x.nodes('i') AS y (i)
    ) v where @list is not null
  )
GO;
      
-- select * from utils.SplitStrings ('hi#red#blue#greee', '#')