jetSQL - SELECT . . . LIKE 'XYZ' . . . O

Results 1 to 3 of 3

Thread: jetSQL - SELECT . . . LIKE 'XYZ' . . . O

  1. #1
    Join Date
    Dec 1969

    Default jetSQL - SELECT . . . LIKE 'XYZ' . . . O

    I have a &#039;little&#039; sorting problem due to weird stored names...<BR>I hava a table called ENG which contains a few hundered post<BR>with a unique name like these examples;<BR>1-10-1-1<BR>1-10-10-1<BR>1-10-11-1 etc...<BR><BR>The problem:<BR><BR>I wish to sort them and order them by name in the sql query,<BR>but it comes out in the order 1-10-10-1, 1-10-1-1, 1-10-11-1 !!<BR>This is of course depending on the format (should have been "01-10-01-01").<BR><BR>But never the less, now they look this way and my question is if anyone of you guys (and girls)<BR>can think of a way to get them sorted as if there<BR>were zeros in front of the single numbers... 01 02 03...10 11.<BR><BR>Thanks in advance for any suggestions!<BR><BR>// Kalpa

  2. #2
    Join Date
    Dec 1969

    Default I know you probably don't want to

    hear this ; )<BR><BR>However, if the data really should be stored with the zeroes in front and you only have a couple hundred records, why don&#039;t you just bite the bullet and fix the data.<BR><BR>I know it may take you a little time, hoever in the long run it will be "correct" and its better to do it now than when you have a couple thousand records.<BR><BR>Of course, you&#039;ll also have to "fix" what ever process is creating these values too.<BR><BR>Anyways just my two cents <BR><BR>Good luck<BR>Pete

  3. #3
    Join Date
    Dec 1969

    Default Absolutely agree!

    It would be pretty trivial to write a simple ASP (or VBS, for use with WSH) page that runs through the entire table, one time, fixing that field for each record:<BR><BR>&#060;%<BR>... create conn and rs, open conn ...<BR><BR>RS.Open "tablename", conn, adOpenStatic, adLockPessimistic<BR>Do Until RS.EOF<BR> temp = Split( RS("problemField"), "-" )<BR> For i = 0 To UBound(temp)<BR> temp(i) = Right("000" & temp(i), 3 ) &#039; if 3 digits will permanently be enuf<BR> Next<BR> RS("problemField") = Join( temp, "-" )<BR> RS.Update<BR> RS.MoveNext<BR>Loop<BR>%&#062;<BR><BR>Simple as that. <BR><BR>In this expression:<BR> Right("000" & temp(i), 3 ) <BR>just put in as many zeroes as you need and match that with the number at the end. "0000" and 4 for four digits.<BR><BR>(Yes, I know, you can get away with "000" and 4 for four digits; call me paranoid.)<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts