Avatar of maqskywalker

asked on 

using while loop to iterate through a string and handle a comma

I'm using sql server 2019.

I have this query.

-- declare variables
declare @tempstr varchar(2000)
declare @col varchar(2000)
declare @table nvarchar(2000)

-- Step 1 - Create temp table called tbl with one column called in and set its value to this string
CREATE TABLE #tbl (line VARCHAR(1000))
-- insert record into table
VALUES ('"Last Name","FirstName","Email Address","Order of Operations","One, Two and Multistep Equations"');

-- Step 2 - save into variables
SET @col = ''
SET @tempstr = (SELECT TOP 1 RTRIM(REPLACE(Line, CHAR(9), ',')) FROM #tbl)

-- Step 3 - iterate through the string to create table column names
WHILE CHARINDEX(',',@tempstr) > 0
      SET @col=@col + LTRIM(RTRIM(SUBSTRING(@tempstr, 1, CHARINDEX(',',@tempstr)-1))) + ' varchar(100),'     

      SET @tempstr = SUBSTRING(@tempstr, CHARINDEX(',',@tempstr)+1, len(@tempstr)) 
SET @col = @col + @tempstr + ' varchar(100)'

-- Step 4 - create a table called Algebra1Scores with the column names created in step 3
SET @table = 'CREATE TABLE Algebra1Scores (' + @col + ')'

-- view the value stored in the variable called @col
SELECT @table

Open in new window

When I run this query i get this result:

In this screen shot the item in red is my issue.


My desired result should be this:

CREATE TABLE Algebra1Scores ("Last Name" varchar(100),"FirstName" varchar(100),"Email Address" varchar(100),"Order of Operations" varchar(100),"One, Two and Multistep Equations" varchar(100))

How do I fix my query to get this result?
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
Scott Pletcher

8/22/2022 - Mon