March 19, 2009 at 8:04 am
Hi,
I have Following Code:
My issue is i am not able to Extract Every thing the output is expected as Follow:
1 Program Management Manage Ongoing Program Activities 01 Jan 07 01 Jan 07 0 \\
2 Program Management Manage Progress Reporting 01 Jan 07 01 Jan 07 0 \\
but some how loop is going on and on....can any body suggest anything which will be totally dynamic is there is more data this below code should run.
DECLARE
@UserName Varchar(MAX),@UserName3 Varchar(MAX),
@UserName1 Varchar(MAX),
@UserName2 Varchar(MAX),@i INT,@Pos1 INT,@LENGHT INT,
@Pos2 INT
BEGIN
SET @UserName = '03/12/2009 07:48 PM Subject New Assignment [El Project] Project Name: El Project Manager: Administrator The above captioned project is uploaded in the system and certain tasks are assigned to you. Below please find your task description. S.No. Phase Task Name Start Date Finish Date % Comp // 1 Program Management Manage Ongoing Program Activities 01 Jan 07 01 Jan 07 0 \\ // 2 Program Management Manage Progress Reporting 01 Jan 07 01 Jan 07 0 \\ '
SET @Pos1 = CHARINDEX('//', @UserName, 1)
SET @Pos2= CHARINDEX('\\', @UserName,1)
print @Pos1
Print @Pos2
SET @UserName1 = SUBSTRING(@UserName,@Pos1,@Pos2)
SET @LENGHT=Len(@UserName)
print @UserName1
SELECT @i=1
A:
SET @Pos1 = CHARINDEX('//', @UserName1, @i)
SET @Pos2= CHARINDEX('\\', @UserName1,@i)
Print @pos2
SET @UserName2 = SUBSTRING(@UserName1,@Pos1+2,@Pos2)
print @UserName2 --U2
SELECT @i=@i+1
WHILE @i<@Pos2
BEGIN
SET @UserName3=@UserName2
goto A
END
END
Any Help would be greatly appriciated.
Thanks
Parth
March 19, 2009 at 9:21 am
I just ran your code on my machine. It looped 87 times in less than 1 second, and then stopped. No infinite loop. Did I misunderstand that part of what you were asking?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 19, 2009 at 9:29 am
You seem to be trying to select the data between // and \\. Is that correct?
If so, then try this:
declare @UserName varchar(max);
--
SET @UserName = '03/12/2009 07:48 PM Subject New Assignment [El Project] Project Name: El Project Manager: Administrator The above captioned project is uploaded in the system and certain tasks are assigned to you. Below please find your task description. S.No. Phase Task Name Start Date Finish Date % Comp // 1 Program Management Manage Ongoing Program Activities 01 Jan 07 01 Jan 07 0 \\ // 2 Program Management Manage Progress Reporting 01 Jan 07 01 Jan 07 0 \\ '
--
select substring(@UserName, number+2, charindex('\\', @UserName, number)-number),
charindex('\\', @UserName, number)-number
from dbo.Numbers
where substring(@UserName, number, 2) = '//'
and number <= len(@UserName);
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 19, 2009 at 9:38 am
You should definitely try the solution proposed by GSquared it will scale and perform much better than a loop.
If you don't have a numbers table you can create a CTE to replace it like this:
/*
this builds a tally or numbers cte to help do the split
see this article http://www.sqlservercentral.com/articles/TSQL/62867/
for an explanation of the tally table and the split function that GSquared uses
*/
;WITH cteTally AS
(
SELECT TOP 10000
ROW_NUMBER() OVER(ORDER BY AC.NAME) AS n
FROM
sys.all_columns AS AC Cross Join
sys.all_columns AC2
)
Edit: put code in code block
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 19, 2009 at 9:49 am
Hi
Thanks for the prompt reply but why it is running for 87 times,
it should give me those 2 lines and then should stop
Why is this????
any solution for this???
March 20, 2009 at 7:45 am
It's running 87 times because it increases by 1 each time, and there are 87 characters in the string.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply