need help for my query to get the number pattern.

Question

need help for my query to get the number pattern.

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

jkp2311 (6/25/2011)
Hi Bitbucket
I find another solution...
declare @Vint int
declare @vcount int
declare @vintnew varchar(200)
declare @vintnew1 varchar(200)
set @Vint = 1
set @vintnew = cast(@vint as varchar(200))
while @Vint < 5
begin
set @Vint = @Vint + 1
set @vintnew = @vintnew + cast(@vint as varchar(200))
end
print @vintnew
set @vcount = LEN(@vintnew)
while @vcount > 0
begin
set @vintnew1 = substring(@vintnew ,1 ,@vcount-1)
set @vcount = @vcount -1
print @vintnew1
end
go

Again did you read my answer Posted Today @ 9:57 AM to LutZM post ?

Note that in that posting I said

when in truth it is the worst possible method

. And my comment

Now mr/mrs jkp2311 please, please examine how LutzM's code works, for his solution (using a CTE) is a most powerful tool

Then read and re-read Jeff Moden's post. Learn how to solve problems without loops (WHILE statements) as Jeff Moden and LutZM have said.

I am now regretting having posted the WHILE code, instead of in effect forcing you to learn how NOT to use a loop.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jay Pete SSCommitted Points: 1945 More actions · Answer 2

Hi Bitbucket....

I will follow the method as Lutz told me as well as jefferson..

but let me know the good material about CTEs.

so i can learn

Thanks a lot all people...

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 3

jkp2311 (6/25/2011)
Hi Bitbucket....
I will follow the method as Lutz told me as well as jefferson..
but let me know the good material about CTEs.
so i can learn
Thanks a lot all people...

My name is not "jefferson" and I actually take exception to you warping my name. It's just not polite. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ron McCullough SSC Guru Points: 63877 More actions · Answer 4

You can use Google to find many, many helpful articles for CTE's. Now to start you off:

http://msdn.microsoft.com/en-us/library/ms190766(v=SQL.90).aspx

and

http://www.simple-talk.com/sql/t-sql-programming/sql-server-cte-basics/

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jay Pete SSCommitted Points: 1945 More actions · Answer 5

Hi Jeff,

I am so Sorry...

it was my mistake...

i apolozige to you...

And Bitbucket....Thanks to you....

i got it...

Ron McCullough SSC Guru Points: 63877 More actions · Answer 6

jkp2311 (6/25/2011)
Hi Jeff,
I am so Sorry...
it was my mistake...
i apolozige to you...
And Bitbucket....Thanks to you....
i got it...

I am sure that both myself, LutzM and Jeff Moden are glad that you have as they say "seen the light" and will hence forth start using CTE's at the appropriate times. If in the future you have a specific problem with a CTE, please post to a new forum following the outline for table definition(s), sample data and what you have attempted and the results required form the sample data. All this can be done easily by clicking on the first link in my signature block to read how and to obtain T-SQL statements that allow you to do so quickly and easily.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 7

jkp2311 (6/25/2011)
Hi Jeff,
I am so Sorry...
it was my mistake...
i apolozige to you...

Accepted and much appreciated. We're good.

Shifting gears, the reason why I keep asking why you want to do this is because it may drive how it should be done.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 8

Let's put a slightly different slant on this problem and make it super simple in the process. 🙂 Compare the complexity of the While Loop and CTE solutions to the following:

DECLARE @String VARCHAR(8000);

SELECT @String = REPLICATE('1234567890',10);

SELECT LEFT(@String,t.N)

FROM dbo.Tally t

WHERE t.N BETWEEN 1 AND LEN(@String)

ORDER BY t.N DESC;

If you don't know what a Tally Table is or how it can be used to replace certain While Loops and other forms of RBAR, please see the following article. The article also explains how to build a Tally Table... without a loop there, either. 😉

http://www.sqlservercentral.com/articles/T-SQL/62867/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)