|
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
1) Use a Cursor or While loop and use a split function to split the strings based on '\' character
2) 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.
3) Then you apply the Order by operation.
Hope this helps
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.txt
bop.txt
my_file_name.txt
newFile.txt
N.B.~ You must change the database design, as I posted in my earlier reply
Hope this helps.
Niladri Biswas
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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.)
oooo, the Jedi's will feel this one....
|
|
|
|
|
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
|
|
|
|
|
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.
const char* extractFileName(const char* s){
while( *s != '\0' ) ++s;
while( *s != '\\') --s;
++s;
return s;
}
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);
}
|
|
|
|
|
DECLARE @ventyxdbname varchar(50)
SET @ventyxdbname = 'OUTLOG_PACE'
/* */
USE @ventyxdbname;
This gives:
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '@ventyxdbname'.
Is it not possible to use a variable in a script for the USE command?
|
|
|
|
|
use nvarchar datatype instead of varchar
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Hi,
Changed it to nvarchar, but still the same message.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '@ventyxdbname'.
It definitely does not seem to like a variable with USE. Could it be that tis is like the CREATE TABLE command where you cannot use a variable directly?
|
|
|
|
|
declare @DBName varchar(20)
declare @Str varchar(100)
set @DBName = databasename
set @Str = 'use ' + @DBName
exec (@Str)
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Many thanks for trying! I am afraid that does not work either.
declare @DBName varchar(20)
declare @Str varchar(100)
set @DBName = 'OUTLOG_PACE'
set @Str = 'use ' + @DBName
exec (@Str)
select * from dbo.OUT_tbl_Request
--------------
Error message:
Msg 208, Level 16, State 1, Line 8
Invalid object name 'dbo.OUT_tbl_Request'.
The table name is correct, but this is the result if I start the query in master.
Not to worry, there are only 4 places in the script where the database name needs to be changed. I was just trying to be lazy as it is quite a long script and I did not want to miss one. I will use Ctrl+H !!
|
|
|
|
|
What happens if you put a go statement after the exec? For some reason I remember something similar causing me problems and adding a go statement fixed it. Could be completely wrong as I don't have SQL Server in front of me.
|
|
|
|
|
I think I have found the answer - it don't work!!!
From Microsoft
Using EXECUTE with a Character String
In earlier versions of SQL Server, character strings are limited to 8,000 bytes. This requires concatenating large strings for dynamic execution. In SQL Server 2005, the varchar(max) and nvarchar(max) data types can be specified that allow for character strings to be up to 2 gigabytes of data.
Changes in database context last only until the end of the EXECUTE statement. For example, after the EXEC in this following statement is run, the database context is master.
USE master; EXEC ('USE AdventureWorks; SELECT EmployeeID, Title FROM HumanResources.Employee;');
Thanks very much for trying anyway!
|
|
|
|
|
Hi all,
I would need to sync data between two databases with diferent structure. It happens that in one Database (A) i have just 2 or 3 tables (with lot of fields), and on the other database i should have the same data splitted on many other tables. To Sync this i will to create a lot of triggers that are fired on both database tables but some fields data type were also changed like: Where i have field data type NVarchar() on database A the same correspondent field on Database B is Int(). On database A the field can assume the values Yes/No/In Progress but on databse B the same field assumes the values 1/2/3.
Are a lot of triggers to do!
Wich should be the best way to do this? Wich is the best way to convert the data between tables?
Is there some tool to help me doing this like Sybase ETL?
Best Regards,
|
|
|
|
|
You could possibly use SSIS to achieve most of what you're after. How current do both databases need to be?
|
|
|
|
|
|
Hi Guys
Heres a nice easy one for you DBA's out there to settle my curiosity. (Not sure if this is SQL Specific)
Up until recently, I thought that a database schema described the database objects (tables, fields, data types, relationships etc), where the schema is really a way of orgainising and securing data with a prefix. (eg. to restrict a table of orders to just sales, a schema of sales is created and only sales people will be granted access. The table name will be Sales.Orders)
My question is, what is what I originally thought was a schema called? (the description of the database)
Thanks...
oooo, the Jedi's will feel this one....
|
|
|
|
|
|
You are basically correct in your original thought.
"... the schema defines the tables, the fields in each table, and the relationships between fields and tables." (Wikipedia)
What you got confused is database object security. Something like Sales.Orders would indicate that there is a table,Orders, and it's owner is Sales.
To add to the confusion is the fact that Oracle calls things "schemas" within one database. So you can create a schema for Sales and it will hold all of the objects owned by Sales. Ugh. Have I confused you yet ?
Basically, you were right in the first place. Just don't confuse schemas with how database engines provide security to various objects. (tables,views,procedures,etc)
david
fluent in both MS-SQL and Oracle
|
|
|
|
|
Gotcha... So it's kind of dependent on database brand?
As with SQL, when you create a schema object, and assign a table to that schema to create Sales.Orders, this schema is more of a way of organising and defining ownership, rather than security?
I'm trying to get into databases a bit more. It seems a pretty meaty subject...
oooo, the Jedi's will feel this one....
|
|
|
|
|
Well ... defining ownership implicitly has security implications right?
User "Sales" has full access to table Sales.Order, but user "A" will not have access to this table unless specifically granted.
Are you looking to develop databases for use with a website or a client-server windows application ? The reason for this is that with a website or N-Tier applicaiton typically the database and all of its objects are owned by "dbo" (database owner), you then create 1 user "myappuser" that is granted access to that database and you build your security in the application, not the database. The user, "myappuser" has full access to all aspects of the database and your individual users may need only read access to most tables and update to a select few.
Think about a large company with 10,000 users. Would you want the database administrator to have to create 10,000 user accounts and have 10,000 user connections when the system is running ? Nope. You have 1 user connected to the database and he makes all of the requests on behalf of the users. (This is a very simple description, but you should get the idea.)
CodeProject is a great resource for learning and the community is very helpful.
Keep asking questions and have fun learning.
David
|
|
|
|
|
That is great. Thank you.
I'm mainly looking at building databases for applications. They will be large and complex databases, so I was trying to find ways of simplifiying things. As you said, as users will be using the application to make use of the databases, there would be no reason to complecate things by creating lots of users and schema's (as in the db owner), and to have one that the applicaiton controls.
That in itself saved me some potential headaches. Thanks.
oooo, the Jedi's will feel this one....
|
|
|
|
|
Remember that when you have one user connected to the database and your security is controlled by your application you cannot use triggers to build an auditing system. From the trigger's perspective, there is always one user making changes to the database. If your application needs auditing, remember that you will have to pass the applicaiton logon name through your routines to log it in your audit tables.
Also consider how you would handle security for reporting. You don't want anyone connecting to your database with a third-party tool because it circumvent any security you may have built.
You can solve this by:
1) Don't allow anyone else but your application to connect to the database
2) Create special reporting views or stored procedures that can be used by a third-party tool and still conform to your security requiremnets.
Jeez, lots of things to think about right? Can you tell that I've made a few mistakes in datatbase/application design in my past? I've got the lumps and gray hairs to show for it.
|
|
|
|
|
There is a lot to think about. Way more involved than I first thought. All good though. Creating something so complex (provided it all works ok) must give real job satisfaction. I've only done basic database stuff in the past, queries, basic stored procs. I've not really thought about automated auditing, and I didn't realise you could use triggers. This is good stuff.
Sounds like you have plenty of experiance in this game. I have to say, I can see database design and administration playing quite a major part in my career.
oooo, the Jedi's will feel this one....
|
|
|
|
|