August 5, 2008 at 9:03 am
I keep getting the following error:
Incorrect syntax near the keyword 'ELSE'.
My code looks like this:
WHILE @currentpos <= LEN(@workstring)
BEGIN
SET @currentchar=SUBSTRING(@workstring, @currentpos, 1)
IF ASCII(SUBSTRING(@workstring, @currentpos, 1)) = 32
SET @outstring = SUBSTRING(@workstring,1,@currentpos)
SET @currentpos=LEN(@workstring)
ELSE
SET @currentpos=@currentpos+1
END
RETURN(@outstring)
Any ideas?
August 5, 2008 at 9:15 am
You need a BEGIN / END block between IF and ELSE if the IF is followed by more than one statement.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 5, 2008 at 9:17 am
Use BEGIN/END in IF/ELSE, you'll never go wrong.
WHILE @currentpos <= LEN(@workstring)
BEGIN
SET @currentchar=SUBSTRING(@workstring, @currentpos, 1)
IF ASCII(SUBSTRING(@workstring, @currentpos, 1)) = 32
BEGIN
SET @outstring = SUBSTRING(@workstring,1,@currentpos)
SET @currentpos=LEN(@workstring)
END
ELSE
BEGIN
SET @currentpos=@currentpos+1
END
END
RETURN(@outstring)
-- Gianluca Sartori
August 5, 2008 at 9:23 am
HI,
Might be worth looking into changing that code to use a Tally table which should give you must better performance 😉
also something good to learn if you don't knwo much about it
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 5, 2008 at 9:39 am
Here are few examples of how to do this differently depending what you want.
1 - example is yours.
2 - example is just returning the first word before the string like yours
3 - uses a tally table and returns each word that is seperated by a space.
You change the delimiter Variable depending on what you want to seperate by.
DECLARE @currentpos INT
DECLARE @workstring VARCHAR(100)
DECLARE @outstring VARCHAR(100)
DECLARE @currentchar VARCHAR(100)
DECLARE @delimiter CHAR(1)
SELECT
@workstring = 'This is a tester',
@currentpos = 0,
@delimiter = ' '
--EXAMPLE 1
WHILE @currentpos <= LEN(@workstring)
BEGIN
SET @currentchar=SUBSTRING(@workstring, @currentpos, 1)
IF ASCII(SUBSTRING(@workstring, @currentpos, 1)) = 32
BEGIN
SET @outstring = SUBSTRING(@workstring,1,@currentpos)
SET @currentpos=LEN(@workstring)
END
ELSE
BEGIN
SET @currentpos=@currentpos+1
END
END
SELECT @outstring
--EXAMPLE 2
SELECT SUBSTRING(@workstring,0,CHARINDEX(CHAR(32),@workstring))
--EXAMPLE 3
SELECT SUBSTRING(@workstring+@delimiter, n,
CHARINDEX(@delimiter, @workstring+@delimiter, n) - n)
FROM Tally
WHERE n <= LEN(@workstring)
AND SUBSTRING(@delimiter + @workstring,
n, 1) = @delimiter
ORDER BY n
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply