SQL Query

theCPkid

My database holds the complete file path like D:\myfolder\my_file_name.txt & C:\newFile.txt So, when I sort this, it is sorted according to complete path. Instead, I want to sort it based only on the file name (my_file_name.txt & newFile.txt). Is there any way to write such a query? (If I will implement a sort by myself on file names, it would be slow compared to SQLite sort). Please suggest.

Niladri_Biswas

I think your design is wrong. You can store the File Path in 1 column and the corresponding File Names in another column and then perform the sorting on File Names. In your case, you have to chuck out the File Names from the entire file path and then you have to store it may be in a temporary file and then you have to perform then sorting.

However, the solution is

Use a Cursor or While loop and use a split function to split the strings based on '\' character
Extract the last string and store it in some temporary table or table variables or you can make your can write a table valued split function.
Then you apply the Order by operation.

Hope this helps :)

Niladri Biswas

Niladri_Biswas

In my earlier post, I have given the idea. Here is the solution Say , I have a table tblFileNameSort where I am storing the entire file path i.e. FILEPATH ---------- D:\myfolder\my_file_name.txt C:\newFile.txt C:\abcFile.txt E:\bop.txt I have written a table valued split function

--Created by Niladri Biswas
ALTER FUNCTION [dbo].[fnSplit]
(@oldstring as varchar(100),@delimeter as varchar(1))
RETURNS @mytab table(counter int,stringval varchar(100))
AS
Begin

	Declare @newstring as varchar(100)
	Declare @pos as int
	Declare @i as int
	Declare @c as int	

	set @newstring = '';		
	set @i = 1
	set @c = 0

	set @pos = CHARINDEX(@delimeter, @oldstring) 

	WHILE (@i != 0)

		Begin

			set @c = @c +1
			insert into @mytab(counter,stringval) values(@c,@newstring + Substring(@oldstring,0, @pos))

			
			set @oldstring = Substring(@oldstring,@pos+1,len(@oldstring))

			set @pos = CHARINDEX(@delimeter, @oldstring)

			set @i = @pos;
			if (@i = 0)
            Begin
                set @i = 0;
					set @c = @c +1
            
				insert into @mytab(counter,stringval) values(@c,@newstring + @oldstring)
				
            End

		End

		return

End

And my stored proc reads like the following

--Created by Niladri Biswas
ALTER PROCEDURE Dbo.SortTxtFile
-- Add the parameters for the stored procedure here

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- VARIABLE DECLARATION
DECLARE @FILEPATH VARCHAR(50)

DECLARE @FINALSTORAGE TABLE 
(
	FILENAMES VARCHAR(100)
)

-- STEP 1: DECLARE A CURSOR
DECLARE MYRANDOMCURSOR CURSOR FOR

	SELECT    FILEPATH
	FROM      tblFileNameSort

-- STEP 2: OPEN THE CURSOR
OPEN MYRANDOMCURSOR 

FETCH MYRANDOMCURSOR INTO @FILEPATH

-- STEP 3: START THE LOGIC

WHILE @@Fetch\_Status = 0

BEGIN

	-- STEP 4: INSERT THE COMBINED RECORDS INTO TABLE      
            --          @FINALSTORAGE

		INSERT INTO @FINALSTORAGE

		SELECT stringval FROM dbo.fnSplit(@FILEPATH,'\\')

		WHERE stringval LIKE '%.txt'
		
	
-- STEP 5: GET THE NEXT RECORD
FETCH MYRANDOMCURSOR INTO @FILEPATH

END

--STEP 6: CLOSE THE CURSOR
CLOSE MYRANDOMCURSOR

--STEP 6: DEALLOCATE THE CURSOR
DEALLOCATE MYRANDOMCURSOR

SELECT \* FROM @FINALSTORAGE ORDER BY FILENAMES

END
GO

The output is FILENAMES ------------- abcFile

Mycroft Holmes

I love being a smartass, try this...

Select *
from Tablename
order by REVERSE(LEFT(REVERSE(path),(CHARINDEX('\',REVERSE(path))-1)))

Not tested but the string massaging works

Never underestimate the power of human stupidity RAH

Paul Unsworth

I just happened to spot this post, and I have to say, this is pure genius. I'm new to SQL, but with the applications I'm writing, that snippet of code could come in handy for a lot of things. Thanks (even though i'm nothing to do with this post.) :-D

oooo, the Jedi's will feel this one....

theCPkid

Brilliant, _insert_synonyms_of_brilliant_here_ man! My knowledge of SQL is only around 1 hour old out of which 50 minutes was around 2 years back. I tried ltrim(X,Y) of SQLite but it returned the error that ltrim is not defined although this function is there in docs on SQLite website.. may be some version problems. Then, I wrote my own function (based on functions in func.c) extractFileName(..) and tried to use "load_extension" to load the c file but could not make it work too. Finally, I opened SQLite source file "func.c" and added the following two functions and an entry "{ "extractFileName", 1, 0, SQLITE_UTF8, 0, extractFileNameFunc }," in "sqlite3RegisterBuiltinFunctions" and in my SQL query, changed " ORDER BY filePath" to " ORDER BY extractFileName(filePath) " and all worked well!! Thank you. CP Rocks otherwise where in world one can find ppl like you.

/*
Extracts the file name from the file path. If file path is D:\myFolder\new1.txt, then it returns
new1.txt. not in original SQLite.
*/
const char* extractFileName(const char* s){
while( *s != '\0' ) ++s;
while( *s != '\\') --s;
++s;
return s;
}

/*
Extracts the file name from the file path. If file path is D:\myFolder\new1.txt, then it returns
new1.txt. not in original SQLite.
*/
static void extractFileNameFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
const char *z;

assert( argc==1);

if( SQLITE_NULL==sqlite3_value_type(argv[0]) ){
sqlite3_result_null(context);
return;
}
z = sqlite3_value_text(argv[0]);
sqlite3_result_text(context, extractFileName(z), -1, SQLITE_TRANSIENT);
}

theCPkid

Hey Niladri, thanks man for the code. But I am totally illiterate as far as SQL goes. And I am using SQLite so I guess the functions calls etc are different. So, I canot even directly copy your code and use as I dont know where to put what and how to pass parameters etc.. Anyway, I solved my problem by using the Holmes brilliant answer. many thanks for the help. :)

Mycroft Holmes

I would love to take credit for it but I probably snaffled it from a tutorial in SQL 6.5, it has been around for a LOOOOnng time.

Never underestimate the power of human stupidity RAH