07-31-2023, 09:41 AM
I use the answer of frederic but this did not work in SQL Server 2005
I modified it and I'm using `select` with `union all` and it works
DECLARE @str varchar(max)
SET @str = 'Hello John Smith how are you'
DECLARE @separator varchar(max)
SET @separator = ' '
DECLARE @Splited table(id int IDENTITY(1,1), item varchar(max))
SET @str = REPLACE(@str, @separator, ''' UNION ALL SELECT ''')
SET @str = ' SELECT ''' + @str + ''' '
INSERT INTO @Splited
EXEC(@str)
SELECT * FROM @Splited
And the result-set is:
id item
1 Hello
2 John
3 Smith
4 how
5 are
6 you
I modified it and I'm using `select` with `union all` and it works
DECLARE @str varchar(max)
SET @str = 'Hello John Smith how are you'
DECLARE @separator varchar(max)
SET @separator = ' '
DECLARE @Splited table(id int IDENTITY(1,1), item varchar(max))
SET @str = REPLACE(@str, @separator, ''' UNION ALL SELECT ''')
SET @str = ' SELECT ''' + @str + ''' '
INSERT INTO @Splited
EXEC(@str)
SELECT * FROM @Splited
And the result-set is:
id item
1 Hello
2 John
3 Smith
4 how
5 are
6 you