As long as you can connect to a SQL Server instance, you should be able to create temporary tables.
Here is one solution:
DECLARE @Resultat NVARCHAR(MAX)='2001:1, 2001:2, 2018:1, 2018:2'
DECLARE @Position INT=LEN(@Resultat)
DECLARE @CommaSeperatedValue NVARCHAR(MAX)=''
DROP TABLE IF EXISTS #TempTable
CREATE TABLE #TempTable(YearMonth nvarchar(50))
WHILE CHARINDEX(',', @Resultat) > 0
BEGIN
SELECT @Position = CHARINDEX(',', @Resultat)
SELECT @CommaSeperatedValue = SUBSTRING(@Resultat, 1, @Position-1)
INSERT INTO #TempTable
SELECT LTRIM(RTRIM(@CommaSeperatedValue))
SELECT @Resultat = SUBSTRING(@Resultat, @Position+1, LEN(@Resultat)-@Position)
END
--Add Last Value with TRIM
IF (LEN(LTRIM(RTRIM(@Resultat)))>0)
BEGIN
INSERT INTO #TempTable
SELECT LTRIM(RTRIM(@Resultat))
END
SELECT * FROM #TempTable
Result:
Hope this can help you.
Best regards,
Sean
ASP.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today. Learn more >
Member
1 Points
20 Posts
Splitting a varchar variable into several varchars separated by comma
Jul 20, 2020 11:10 AM|szejli|LINK
Hello,
I have a varchar variable in SQL SERVER set like this:
@Resultat='2001:1, 2001:2, 2018:1, 2018:2'
I want to have this variable splitted into 4 varchar variables as follows:
2001:1
2001:2
2018:1
2018:2
My sql server doesn't support the function STRING_SPLIT and I don't have rights to create a table or a function.
How can I achieve this ?
Thanks in advance.
Contributor
3020 Points
889 Posts
Re: Splitting a varchar variable into several varchars separated by comma
Jul 20, 2020 12:04 PM|Sean Fang|LINK
Hi szejli,
As long as you can connect to a SQL Server instance, you should be able to create temporary tables.
Here is one solution:
Result:
Hope this can help you.
Best regards,
Sean