Splitting a varchar variable into several varchars separated by comma RSS

• • Reply
  szejli

  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.

• • Reply
  Sean Fang

  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:

  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 >