Information générale
Titre:
String manipulation
I want to send in a string like 'Bob,Jim,Penny' and use my while loop to insert individual names into a temporary table...
I have done multiple tests.. and the line:
Set @newstr = substring(@list,@x+1,@lenofstring)
I think is messing up.
Thanks for the help... I have included the code..
Alvin
CREATE TABLE #testtable (var1 varchar(8000))
DECLARE @str varchar(50)
DECLARE @sql varchar(8000)
DECLARE @tbl sysname
DECLARE @custname varchar(8000)
DECLARE
@b numeric,
@pos int,
@x int,
@list varchar(8000),
@newstr varchar(8000),
@newpos int,
@lenofstring int,
@count int
SET @custname = 'Art,Creative Kitchens'
SET @list = @custname
SET @x = 1
Set @lenofstring = len(@list)
Set @count =0
SET @pos = charindex(',',@list,@x)
WHILE @pos > 0
BEGIN
Set @count=@count+1
Set @str = substring(@list,@x,@pos-@x)
Set @x = @pos
-- THIS LINE IS MESSED UP
Set @newstr = substring(@list,@x+1,@lenofstring)
Set @pos = charindex(',',@newstr,@x)
if @count >=1
BEGIN
INSERT INTO #testtable SELECT customer.customername
FROM customer INNER JOIN orderdetails ON
customer.customerid = orderdetails.customerid
WHERE customer.customername = @str
print @str
END
ELSE
BEGIN
INSERT INTO #testtable SELECT customer.customername
FROM customer INNER JOIN orderdetails ON
customer.customerid = orderdetails.customerid
WHERE customer.customername = @newstr
print @newstr
END
if @pos <= 0
BEGIN
Set @str = substring(@list,@x,len(@list))
INSERT INTO #testtable
SELECT customer.customername
FROM customer INNER JOIN orderdetails ON
customer.customerid = orderdetails.customerid
WHERE customer.customername = @str
END
END
SELECT * FROM #testtable
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement