I just want it to return 1 2
WITH
CTE1 AS
(
select value
UNION ALL
select value=value+1
FROM CTe1
WHERE value =2
)
select * from cte1
how come that doesnt work.
-
please go and read Books online for CTE syntax.Mitch Wheat– Mitch Wheat2011-09-12 01:19:08 +00:00Commented Sep 12, 2011 at 1:19
Add a comment
|
3 Answers
The following will print out 1, 2:
WITH
CTE1 AS
(
select 1 as value
UNION ALL
select value=value+1
FROM CTe1
WHERE value = 1
)
select * from cte1
The problem was that value was not defined for your first CTE clause. I assume you wanted 1. Then the second CTE clause self-references the first one and adds 1.
1 Comment
hidden
pretty weird i tried values=1 before i have to use 1 as value?
The CTE will print 1 and 2 as rows. Is this what you are after?
WITH
CTE1 AS
(
select 1 as value
UNION ALL
select value=value+1
FROM CTe1
WHERE value < 2
)
select * from cte1
Comments
here is a code to search a string for a character macth thank you all.
Begin
with recursiveCTE(matchNumber, foundAt) as (
select 1, charindex(@toFind, @ToSearch, 0)
union all
select matchNumber + 1, charindex(@toFind, @ToSearch, foundAt + 1)
from recursiveCTE where foundAt > 0
)
select
matchNumber as "Match Number",
(case when foundAt = 0 then null else foundAt end) as "Found At"
from recursiveCTE
where foundAt 0 or matchNumber = 1
;
end;