CAHRINDEX

I am trying to tune a query that uses two seperate CHARINDEX functions. The
first:
SELECT @.fieldValue = (SUBSTRING(@.delimitedList, 1, CHARINDEX(',',
@.delimitedList) - 1))
Obviously starts at the first postion and returns the data to the left of
the ','.
The second statement:
SELECT @.delimitedList = SUBSTRING(@.delimitedList, (CHARINDEX(',',
@.delimitedList) + 1), LEN(@.delimitedList))
Limits the the @.delimitedList value to get rid of the value that we have
already captured (essentially moves the second delimited piece into the firs
t
postion). THis is done in a WHILE loop so it continues until all values have
been inserted into a temp table. What I would like is a way to capture the
CHARINDEX value in the first statement to plug in as a starting variable.
Thus making the first statement look like this (or something similar):
SELECT @.fieldValue = (SUBSTRING(@.delimitedList, @.start_pos, CHARINDEX(',',
@.delimitedList) - 1))
Hope this makes sense....any ideas.
TIA, Jordan...
declare @.pos int
declare @.i int
set @.i = 1
set @.pos = charindex(',', @.delimitedList)
while @.pos > 0
begin
SELECT @.fieldValue = SUBSTRING(@.delimitedList, @.i, @.pos - 1)
..
set @.i = @.pos + 1
set @.pos = charindex(',', @.delimitedList, @.i)
end
...
AMB
"JMNUSS" wrote:

> I am trying to tune a query that uses two seperate CHARINDEX functions. Th
e
> first:
> SELECT @.fieldValue = (SUBSTRING(@.delimitedList, 1, CHARINDEX(',',
> @.delimitedList) - 1))
> Obviously starts at the first postion and returns the data to the left of
> the ','.
> The second statement:
> SELECT @.delimitedList = SUBSTRING(@.delimitedList, (CHARINDEX(',',
> @.delimitedList) + 1), LEN(@.delimitedList))
> Limits the the @.delimitedList value to get rid of the value that we have
> already captured (essentially moves the second delimited piece into the fi
rst
> postion). THis is done in a WHILE loop so it continues until all values ha
ve
> been inserted into a temp table. What I would like is a way to capture the
> CHARINDEX value in the first statement to plug in as a starting variable.
> Thus making the first statement look like this (or something similar):
> SELECT @.fieldValue = (SUBSTRING(@.delimitedList, @.start_pos, CHARINDEX(',',
> @.delimitedList) - 1))
> Hope this makes sense....any ideas.
> TIA, Jordan|||That helps, however I need the result set in table form so a delimited strin
g
of '1,2,3,4,5' would look like:
fieldvalue
--
1
2
3
4
5
Is that possible, TIA
"Alejandro Mesa" wrote:
> ...
> declare @.pos int
> declare @.i int
> set @.i = 1
> set @.pos = charindex(',', @.delimitedList)
> while @.pos > 0
> begin
> SELECT @.fieldValue = SUBSTRING(@.delimitedList, @.i, @.pos - 1)
> ...
> set @.i = @.pos + 1
> set @.pos = charindex(',', @.delimitedList, @.i)
> end
> ...
>
> AMB
> "JMNUSS" wrote:
>|||http://www.aspfaq.com/2248
"JMNUSS" <JMNUSS@.discussions.microsoft.com> wrote in message
news:72F0B5F6-93C2-4F0E-94D8-45EE92F2672D@.microsoft.com...
> That helps, however I need the result set in table form so a delimited
> string
> of '1,2,3,4,5' would look like:
> fieldvalue
> --
> 1
> 2
> 3
> 4
> 5
> Is that possible, TIA
>
> "Alejandro Mesa" wrote:
>|||Lot of solutions.
Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html#tryit
Faking arrays in T-SQL stored procedures
http://www.bizdatasolutions.com/tsql/sqlarrays.asp
How do I simulate an array inside a stored procedure?
http://www.aspfaq.com/show.asp?id=2248
AMB
"JMNUSS" wrote:
> That helps, however I need the result set in table form so a delimited str
ing
> of '1,2,3,4,5' would look like:
> fieldvalue
> --
> 1
> 2
> 3
> 4
> 5
> Is that possible, TIA
>
> "Alejandro Mesa" wrote:
>|||Thanks guys, I was able to find what I needed on aspfaq. I really appreciate
the help!!!!
"Alejandro Mesa" wrote:
> Lot of solutions.
> Arrays and Lists in SQL Server
> http://www.sommarskog.se/arrays-in-sql.html#tryit
> Faking arrays in T-SQL stored procedures
> http://www.bizdatasolutions.com/tsql/sqlarrays.asp
> How do I simulate an array inside a stored procedure?
> http://www.aspfaq.com/show.asp?id=2248
>
> AMB
> "JMNUSS" wrote:
>