Existem situações do cotidiano em que precisamos de um recurso sisponível para a grande maioria das linguagens de programação que é o SPLIT, que consistem em dividir um texto em várias partes tendo como base um caractere delimitador.

EX.: "Maria Jose João Paulo Joaquina"
Imagine que você precise destes nomes separados em uma lista. É isto que o split faria.
Vi alguns exemplos na internet, mas não gostei da forma que foi implementado, por isso criei esta que vou apresentar.

if exists (Select 1 from sysobjects where name = 'fn_split')
   drop function fn_split
go

create function fn_split(@texto varchar(8000)) returns @result table(codigo int, palavra varchar(100))
as
begin
  declare @parte varchar(100)
  declare @index int, @count int = 1
  declare @lastIndex int = 0

-- remove os espaços das estrimidades esquerda e direita
  set @texto = rtrim(ltrim(@texto))

-- Remove os espaços repetidos (duplos) 
set @texto = replace(@texto, '  ', ' ') + ' ' 

  set @index = charindex(' ', @texto)

  while @index <> 0
  begin
    set @parte = SUBSTRING(@texto,@lastIndex, @index - @lastIndex)
    insert into @result Select @count , @parte

    set @lastIndex = @index
    set @count = @count + 1
    set @index = charindex(' ', @texto, @lastIndex + 1)
  end

    return
end


select * from fn_split('Teste de divisão de texto em  array  ')
 

Para este código foram utilizadas as seguintes funções:

 

-- Retorna uma parte do texto informado, a partir do número informado, o segundo --parâmetro é a quantidade de caracteres retornados.
SUBSTRING


--  Remove todos os espaços em brando a direita do texto.
RTRIM

--  Remove todos os espaços em branco a esquerda do texto informado.
LTRIM

--  Retorna o texto informado, tendo substituindo todas ocorrências do texto
-- informado no 2º parâmetro pelo 3º parâmetro.
REPLACE

--Retorna a posição de um caractere dentro de um texto. O 3º e ultimo parâmetro é
-- opcional, indica o inicio do texto a ser considerado, caso não seja 
-- informado, a busca inicia-se de 0(zero).
CHARINDEX