Ordering a string field with different lenghts

Question

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jo Pattyn SSC-Dedicated Points: 32350 More actions · Answer 1

if you know the maximum length of the column

select myfield

from mytable

order by convert(char(maxlen),myfield)

Henk Schreij SSCarpal Tunnel Points: 4554 More actions · Answer 2

You asked:

provide me the ordered list by name field, how do I such thing?

i.e:

c:\test\enr.txt

I am not sure what you mean. What is the wanted result? Is this (using the example)

enr.txt

DETALLE.txt

LOTNODOS.txt

Kafka750 SSC-Addicted Points: 479 More actions · Answer 3

SSC-Addicted

Points: 479

March 2, 2006 at 12:53 am

Yeah, it was just that.

Henk Schreij SSCarpal Tunnel Points: 4554 More actions · Answer 4

A solution could be:

Reverse the value of the field, find the backslash, copy it including the backslash, reverse it back and remove the backslash.

When you are absolute sure that there is always a backslash you could also use:

Reverse the value of the field, find the backslash, copy it excluding the backslash, reverse it back.

Here is an example. Type this in the Query Analyzer to see how it wordks.

declare @test-2 varchar(50) set @test-2 = '\\OFI0675P25\VARIOS\DETALLE.TXT'

SELECT REPLACE(REVERSE(SUBSTRING(REVERSE(@Test),1,CHARINDEX('\',REVERSE(@Test)))),'\','')

Kafka750 SSC-Addicted Points: 479 More actions · Answer 5

that finally works properly:

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

Thanks to all for your comments.

Henk Schreij SSCarpal Tunnel Points: 4554 More actions · Answer 6

OK, that is precise what I meant with my second solution.

But it gives a problem when the string is empty, or contains no backslash.

So, not knowing what values in real occur in your database, I gave you the most secure solution.