Post reply

Ordering a variable field.

  • February 28, 2006 at 4:23 am

    #112020

    I am looking for a query which might be able to order by a string. Field values like theses:

     

    \\srvavaa\HISTORICOS\DIARIOS\2090\Ofi6460\Ofi6460_2004-08.txt

    F:\Proyectos\AICEBIS\Modelos CECA\FICH_MODELO.TXT

    \\srvaa8\ficheros\AICE\FICHCRM.txt

    Dynamically I will get the last part, i.e: FICHCRM.txt, FICH_MODELO.TXT and then show it ordered. But I was wondering why bearing on mind that each field own a particular lenght.

    Let me know your comments or thoughts

    Regards,

     

     

     

  • February 28, 2006 at 5:08 am

    #623649

    I work out, finally I've founded the answer to my issue usign REVERSE and CHARINDEX functions:

     REVERSE(substring(REVERSE(DATASOURCE),1,charindex('\',REVERSE(DATASOURCE)) -1))

  • February 28, 2006 at 5:08 am

    #623648

    Hmm, I hope you are not doing this on large recordsets as it will kill performance (no indices could be used).

    In any case, you want to get the text that appears after the last '\' in the filename.  A suggestion for this would be (assuming your column is called filename)

    order by substring(filename, len(filename) - charindex('\', reverse(filename)), 254)

    A better way would be to create another column that is called fileNameSort - the default value for this column would be 

    substring(filename, len(filename) - charindex('\', reverse(filename)), 254)

    You could then sort based on this column and have the column indexed for speed!

    Best of luck

  • February 28, 2006 at 5:09 am

    #623650

    Danm, you beat me by about 10 secs   Shouldn't have tested it before posting!!  oh well

  • February 28, 2006 at 5:10 am

    #623651

    Thanks a lot for your comments Ian.

    All the best,

Viewing 5 posts - 1 through 4 (of 4 total)

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