substring function in tsql

[nayek]

I am looking for substring functionality in tsql. How could I accomplish this?

Here's the scenario, I have a table with two fields, field1 has the file path, and field2 holds the keywords.

field1 field2
------------------- --------------------
c:\20060402.doc bush usa
c:\temp\20060501.doc usa clinton
c:\tmp\20060420.doc usa carter

I want to retreive the records where field2 contains 'usa' and have the records sorted by the last 11 characters of field1.

the sql would look something like

select * from table1 where field1 likep 'usa' order by substr ( field2 , len(field2) - 11 , 11 ) desc ;

And, the result would look like


field1 field2
------------------- --------------------
c:\temp\20060501.doc usa clinton
c:\tmp\20060420.doc usa carter
c:\20060402.doc bush usa

Can you throw me some pointers of how this can be done?

Thanks
Karthik

[Kai]

You could do it with sandr:

... order by sandr('.*.{11}>>=', '\2', field2) desc;