Styjun

I'm trying to create a loop that will pull out one value of a row and store it to a variable(@nameString). I need this to happen for an unknown amount of rows. What should I be referring to when I'm I need this loop to cycle through all of the rows and update my variable each time it loops with the next rows value.

I have tried a few counter loops and they do not cycle through the rows they just keep hitting the same one.

-- Code for parsing a name with multiple parts

DECLARE @nameString as varchar(max),
 @firstSpaceLoc as smallint,
 @secondSpaceLoc as smallint,
 @thirdSpaceLoc as smallint,
 @firstString as varchar(max),
 @secondString as varchar(max),
 @thirdString as varchar(max) 

-- I'm expecting the loop to be below here!
SET @nameString = 'Robert Dobson, Jr.'
--SET @nameString = 'Robert William Dobson, Sr.'

-- How many strings are in the name?
-- Is there one space in the name
SET @firstSpaceLoc = CHARINDEX(' ',@namestring,1)

-- Is there second space in the name
SET @secondSpaceLoc = CHARINDEX(' ', @namestring, CHARINDEX(' ',@nameString,1)+1)

-- Is there a third space in the name
SET @thirdSpaceLoc = 
CASE
 WHEN CHARINDEX(' ', @namestring, 
 CHARINDEX(' ',@nameString,1)+1) = 0 THEN 0
 WHEN CHARINDEX(' ', @namestring, 
 CHARINDEX(' ',@nameString,1)+1) > 0 THEN
 CHARINDEX(' ', @namestring, 
 CHARINDEX(' ', @namestring, 
 CHARINDEX(' ',@nameString,1)+1)+1)
END

SELECT
 @nameString sourceString,
 CASE 
 WHEN @firstSpaceLoc > 0 THEN 'There is one space'
 ELSE 'There is not one space'
 END [Is there one space],
 CASE 
 WHEN @secondSpaceLoc > 0 THEN 'There is a second space'
 ELSE 'There is not a second space'
 END [Is there a second space],
 CASE 
 WHEN @thirdSpaceLoc > 0 THEN 'There is a third space'
 ELSE 'There is not a third space'
 END [Is there a third space]


-- extract and save strings
SELECT

@firstString = 
 CASE
 WHEN @firstSpaceLoc > 0 THEN LEFT(@nameString,CHARINDEX(' ',@namestring,1)-1)
 ELSE @nameString
 END,
 @secondString = 
 CASE
 WHEN @firstSpaceLoc = 0 THEN ''
 WHEN @secondSpaceLoc = 0 THEN 
 RIGHT(@namestring, LEN(@namestring)- CHARINDEX(' ',@namestring,1))
 WHEN @secondSpaceLoc > 0 THEN
 REPLACE (
 SUBSTRING (
 @nameString, 
 CHARINDEX(' ',@namestring,1)+1, 
 CHARINDEX(' ', @namestring, 
 CHARINDEX(' ',@nameString,1)+1) 
 - CHARINDEX(' ',@namestring,1)
 ),
 ',',
 ''
 )
 ELSE ''
 END,
 @thirdString =
 CASE
 WHEN @firstSpaceLoc = 0 OR @secondSpaceLoc = 0 THEN ''
 WHEN @secondSpaceLoc > 0 
 AND @thirdSpaceLoc = 0 THEN
 SUBSTRING (
 @nameString,
 CHARINDEX(' ', @namestring, 
 CHARINDEX(' ',@nameString,1)+1)+1,
 LEN(@nameString)
 ) 
 ELSE RIGHT(@namestring,LEN(@namestring) - @secondSpaceLoc) 
 END


-- Report names
SELECT
 @nameString sourceString,
 @firstString [First string],
 @secondString [Second string],
 @thirdString [Third string]

SELECT
 CASE
 WHEN @thirdSpaceLoc > 0 THEN 
 @thirdString + ', ' + @firstString + ' ' + @secondString
 WHEN @secondSpaceLoc > 0 AND @thirdSpaceLoc = 0 THEN 
 @secondString + ' ' + @thirdString + ', ' + @firstString
 WHEN @firstSpaceLoc > 0 THEN 
 @secondString + ', ' + @firstString 
 WHEN @firstSpaceLoc = 0 THEN 
 @firstString
 END [Reported Name]

Expected results is that this goes through and separate every row in a column with multiple spaces to a long list of name that are separated. This list but a lot longer.

can you try this way:

DECLARE @nameString AS VARCHAR(MAX), 
 @firstString AS VARCHAR(MAX),
 @secondString AS VARCHAR(MAX),
 @thirdString AS VARCHAR(MAX) 

SET @nameString = 'Robert Dobson, Jr.'

DECLARE @tbl TABLE
(
 Id INT IDENTITY(1,1),
 Name VARCHAR(MAX)
)

INSERT INTO @tbl(Name)
SELECT value FROM STRING_SPLIT(@nameString, ' ');


SELECT @firstString = Name FROM @tbl WHERE Id = 1
SELECT @secondString = Name FROM @tbl WHERE Id = 2
SELECT @thirdString = Name FROM @tbl WHERE Id = 3


SELECT
@nameString sourceString,
CASE WHEN LEN(@firstString) > 0 THEN 'There is one space'
 ELSE 'There is not one space'
END [Is there one space],
CASE WHEN LEN(@secondString)> 0 THEN 'There is a second space'
 ELSE 'There is not a second space'
END [Is there a second space],
CASE WHEN LEN(@thirdString)> 0 THEN 'There is a third space'
 ELSE 'There is not a third space'
END [Is there a third space]

SELECT @nameString SourceString, @firstString [First string], @secondString [Second string] , @thirdString [Third string]

SELECT
 CASE
 WHEN LEN(@thirdString) > 0 THEN 
 @thirdString + ', ' + @firstString + ' ' + @secondString
 WHEN LEN(@secondString) > 0 AND LEN(@thirdString) = 0 THEN 
 @secondString + ' ' + @thirdString + ', ' + @firstString
 WHEN LEN(@firstString) > 0 THEN 
 @secondString + ', ' + @firstString 
 WHEN LEN(@firstString) = 0 THEN 
 @firstString
 END [Reported Name]

Sorry if there is some bug in the code, I don't have access to MS-SQL now I've tried to coded directly here.
Your request is quite unusual (I agree with Robert's comment) , but a code simple like this would work.

DECLARE @nameString as varchar(max),
 @firstString as varchar(max),
 @secondString as varchar(max),
 @thirdString as varchar(max)

SET @nameString = 'Robert Dobson, Jr.'

SET @thirdString = REPLACE(@nameString, ',', '')
SET @firstString = LTRIM(SUBSTRING(@thirdString, 1, CHARINDEX(' ', @thirdString)))
SET @thirdString = LTRIM(REPLACE(@thirdString, @firstString, ‘’))
SET @secondString = LTRIM(SUBSTRING(@thirdString, 1, CHARINDEX(' ', @thirdString)))
SET @thirdString = LTRIM(REPLACE(@thirdString, @secondString, ‘’))

SELECT
 @nameString sourceString,
 @firstString [First string],
 @secondString [Second string],
 @thirdString [Third string]

I am making the assumption that you need this to return a dynamic number of columns based on the number of spaces found in nameString. I am using a couple of things there that you will need on your system for this to work correctly.

First is a tally table. I keep one on my systems as a view. Here is the code that I use for this. A tally table is a great tool to help us avoid using loops. You can read more about them here.

create View [dbo].[cteTally] as

WITH
 E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
 E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
 E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS 
 (
 SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
 )
select N from cteTally

The next thing we need is a string splitter. There are a number of these out there and even a built in one on recent versions of sql server. However, even the MS one misses a critical element of ordinal position for each element. Fortunately Jeff Moden has one that is set based, lightning fast and returns the position number of each element.

You can find the code for the DelimitedSplit8K here.

Now we face the challenge of returning a dynamic number of columns. For this you could use a dynamic pivot. That will work just fine. For me I find the syntax for pivot to very obtuse and prefer to use conditional aggregation, or crosstab. In this case it needs to be dynamic.

I created a temp table in my example to represent your table. I included some additional names to the list and some of them have more than 3 strings.

if OBJECT_ID('tempdb..#Something') is not null
 drop table #Something

create table #Something --this represents your current table
(
 nameString varchar(100)
)

insert #Something values
('Robert Dobson, Jr.')
, ('NeoAer')
, ('Ryan Wilson')
, ('Somebody with four names')
, ('another with yet more names')

declare @StaticPortion nvarchar(2000) = 
 'with OrderedResults as
 (
 select nameString
 , stringValue = replace(x.Item, '','', '''')
 , x.ItemNumber
 from #Something s
 cross apply dbo.DelimitedSplit8K(s.nameString, '' '') x
 )
 select nameString';

declare @DynamicPortion nvarchar(max) = '';
declare @FinalStaticPortion nvarchar(2000) = ' from OrderedResults Group by nameString order by nameString';

select @DynamicPortion = @DynamicPortion + 
 ', MAX(Case when ItemNumber = ' + CAST(N as varchar(6)) + ' then stringValue end) as string' + CAST(N as varchar(6)) + CHAR(10)
from cteTally t
where t.N <= 
(
 select top 1 Count(*)
 from #Something s
 cross apply dbo.DelimitedSplit8K(s.nameString, ' ') x
 group by nameString
 order by COUNT(*) desc
)

select @StaticPortion + @DynamicPortion + @FinalStaticPortion

--once you are satisfied that the dynamic sql is correct uncomment the next two lines to execute it.
--declare @SqlToExecute nvarchar(max) = @StaticPortion + @DynamicPortion + @FinalStaticPortion;
--exec sp_executesql @SqlToExecute

If you don't this to return a dynamic number of columns we can employ a similar type of query but it far less complicated.