Compare two varchar arrays one character at time until different

• steve dassin

  SSCrazy

  Points: 2396

  More actions

  July 6, 2007 at 3:34 am

  #717620

  Jeff,

  Congratulations on being recognized by this forum for excellence.

  Given table MyNames ([ID] integer primary key,[Name] varchar(50))

  table numbers (num integer) (table of numbers 1-N)

  This query is the foundation of an sql solution for any @NthRow

  and any number of rows in MyName.

  declare @NthRow int,@MaxID int

  set @NthRow=5

  --Use any query to return number of rows in table NyNames.

  set @MaxID=(select count(*) from MyNames)

  select [Index],RowID,[Name],[From],[To]

  from

  (select

  num as [Index],((num-1)*@NthRow)+1 as [From],num*@NthRow  as [To]

  from numbers

     where num between 1 and @MaxID) as A

  join

  (select [Name],row_number()over(order by [Name]) as RowID

   from MyNames) as B

  on RowID between [From] and [To]

                  

  I do all development in the D4 Dataphor language and use sql server

  as a data store. When appropriate I use pass-through queries in

  sql. I urge people to look at D4. Check out my blog to help rap

  your head around a relational language that has previously never

  existed. I'll help anyone interested in getting started with it.

  This is not trival stuff but well worth the effort. It is high

  grade brain candy. Use sql excellence as a stepping stone to the

  next level.

  best,

  http://www.beyondsql.blogspot.com

• Jeff Moden

  SSC Guru

  Points: 1004313

  More actions

  July 6, 2007 at 8:11 am

  #717700

  Rog,

  I appreciate the compliment.  A long time ago, I realized that the first step towards the "Sql excellence" you speak of is correct identification of the given problem...

  Apparently, though, you've not achieved that same level... you posted code for the wrong question and that makes me not trust you or your product for any form of "excellence".  The OP isn't looking for an Nth row solution as you posted, the OP is looking for a hierarchical tree resolver using an adjacency model that goes to the Nth level.  Worse than that, this is an SQL Server 2000 forum... SQL Server 2000 doesn't even have the Row_Number() function nor any direct equivelent.

  Please stop spamming this forum to sell product.  Contact the owners of the forum and do it the right way by submitting a "product review" like everyone else does.  Probably won't cost a dime and you might even get some customers and referrals from it.  All the spam and personal brown-nosing does is turn folks like me right off.

  --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)