troubleshooting Question

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

Avatar of maqskywalker
maqskywalker asked on
Microsoft SQL ServerSQL
6 Comments3 Solutions72 ViewsLast Modified:
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?
Join our community to see this answer!
Unlock 3 Answers and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros