| |
| Publicado el 08,febrero 2016 - 15:30 |
Hi,
I have some problems with a part of my own build sql string.
I have to extract the filename suffix
.... FROM Files WHERE WL.ExtractString(Files.Filename,-2147483645,'.') = 'pdf'
The number is the constant for "lastrank". The SQL doesn´t run.
Any idea whats the problem is ? |
| |
| |
| | | |
|
| | |
| |
| Publicado el 08,febrero 2016 - 17:49 |
Michael
fExtractPath(MyFilename,fExtension) is your friend |
| |
| |
| | | |
|
| | |
| |
| Publicado el 09,febrero 2016 - 14:09 |
Hi Derek,
I used now "SPLIT_PART" which works ok if the filename has only one "." in his name.
I don´t know how to set "fExtension" in the SQL code, the numeric value doesn´t work and the expression self is not usable. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 09,febrero 2016 - 21:58 |
Michael
Can you not do something along these lines?
SELECT DocumentLog.Filename AS Filename, RIGHT(DocumentLog.Filename,3) AS Formula1 FROM DocumentLog WHERE RIGHT(DocumentLog.Filename,3) = 'pdf' |
| |
| |
| | | |
|
| | |
| |
| Publicado el 09,febrero 2016 - 22:28 |
Hi Derek,
what do you doing with "mynewExceldocument.xlsx" ? :rp: |
| |
| |
| | | |
|
| | |
| |
| Publicado el 10,febrero 2016 - 10:55 |
Michael
Do not understand the 2 dots issue.
Pretty poor naming convention to begin with but in any case it will be the chars following the second dot that identify the file type.
Can you not use a Contains condition...
SELECT DocumentLog.Filename AS Filename, RIGHT(DocumentLog.Filename,4) AS Formula1 FROM DocumentLog WHERE RIGHT(DocumentLog.Filename,4) LIKE '%xls%' This would pick up both xls and xlsx, htm and html, doc and docx - and so on. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 10,febrero 2016 - 11:01 |
Hi Derek,
it´s not so easy lile is seems. I have a database of millions of files. The user can select these files over the database.
You can´t believe what filenames the users create:
My Apointment where all users in room 444 have good ideas at 05.12.2014.msg
and so on. I need a flexible sql command which finds the last part after the last dot with flexible lenght of the suffix. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 10,febrero 2016 - 11:19 |
Michael
Not sure I can help then as I do not believe MySQL offers that functionality - Not that I am a guru in this respect.
Of course the world would be much easier without users but that is not 'our lot' unfortunately.
I understand that filenames created by users can be a bit random but in my experience even they cannot determine the file type identifier.
Sure they may do a Save As and enter '05.12.2014.msg' but if they save it as an xls file the it would be in the file system as '05.12.2014.msg.xls'
Rightly or wrongly it is an .xls file type and the Contains code would find it.
I think you should look at how you are constructing the SQL statement and and set your WHERE condition in your code and not in the statement itself.
How do your users place a request for the files they wish to retrieve? |
| |
| |
| | | |
|
| | |
| |
| Publicado el 10,febrero 2016 - 11:20 |
Michael,
maybe you can add an extra field "file_extension" or "file_type" to the database?
And do the extraction in your own code (with all the options of w-language) and store the result together woith the other info in the record.
Then your SQL will also execute much faster (even better if the new field is numeric and hold values like 1 for Excel, 2 for Word, etc), because part of the job is done beforehand. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 10,febrero 2016 - 11:43 |
Hi Arie and Derek,
I use HFSQL. For me the interesting part is: How to use the wl.commands in the sql editor if the have contstants like "fromend" or "frombeginning". Has anybody expierience with that ?
WL.extractstring is the right command to select the last part of the string after the dot. |
| |
| |
| | | |
|
| | |
| |
| Publicado el 10,febrero 2016 - 12:04 |
Hi Michael,
No point banging your head for too long on this. With WD working the way it does, I would use SQL to bring back the filename or a list of filenames and use WL to do the string handling.
ExtractString might be the way to go, using FromEnd. If the extension isn't always at the end of the filename, you could use the Position function with FromEnd and use a list of 'expected' filename extensions to look for? |
| |
| |
| | | |
|
| | |
| |
| Publicado el 10,febrero 2016 - 12:12 |
Hi Darren,
may be my english is too bad.
I use the WX SQL Editor. I can use all WL commands in the list of WL commands shown in the editor. How can I translate "fromend" (which is a constant in the "extractstring" command) that the SQL Editor understand this ?? |
| |
| |
| | | |
|
| | |
| |
| Publicado el 10,febrero 2016 - 12:18 |
Darren
Curious...
How could a file extension not be at the end of the file name?
If this were the case how would you choose what to open the file with to view the contents? |
| |
| |
| | | |
|
| | |
| |
| Publicado el 10,febrero 2016 - 12:28 |
what about using StringCount to get the number of the last 'point' , thus avoiding the use of a constant?
WL.ExtractString(Files.Filename, WL.StringCount(Files.Filename,".") , '.') |
| |
| |
| | | |
|
| | |
| |
| Publicado el 10,febrero 2016 - 12:43 |
BTW: according to my debugger
fromBeginning=0
and
fromEnd=1
So you can use this straight away.
And the value for lastRank indeed look like
But that's an conversion error in the debugger, it looks like is uses 4-byte instead of 8-byte!!
If you double click the value in the watch-list and use the hex-presentation you will see a value of 0x80000003
And that is 2147483651 decimal according to my Window calculator.
Testing THAT value does work in SQL :spos: |
| |
| |
| | | |
|
| | |
| |
| Publicado el 10,febrero 2016 - 13:15 |
Hi,
The requirements aren't clear are they?
Because users can always enter anything... not always what we'd like them to:
Are we validating what has been entered is correct? i.e. has the user entered ABC.xls.xyz?
Are we trying to find an expected file extension on the end of a filename and use it irrespective of what it is?
Are we trying to 2nd-guess what users have entered i.e. Will we always use what is entered to access a file?
Are finding the 1st valid occurrence of a valid or know file extension?
....I dunno :confused:
Use SQL to get the file name HExecuteQuery(QRY_GetFiles...) without any reference to the extension, returning from the query statement, do a HReadSeekFirst(QRY_GetFiles....), then use (in a loop if there's more than 1 returned) ExtractString or Postion on the QRY_GetFiles.Filename string? |
| |
| |
| | | |
|
| | |
| |
| Publicado el 10,febrero 2016 - 13:32 |
Hi Arie,
no both of it doesn´t work
LastRank:
sSQLText+=StringBuild(" AND ( Masterplan.Plannummer + Masterplan.Index ) = ANY (SELECT Dateien.GesamtPlannummer AS GesamtPlannummer FROM Dateien WHERE WL.Extractstring(Dateien.Dateiname,2147483651,'.') = '%1' )",MyPARA_Qry_Masterplan_Gesamt.sDateiendung)
or
First from End:
sSQLText+=StringBuild(" AND ( Masterplan.Plannummer + Masterplan.Index ) = ANY (SELECT Dateien.GesamtPlannummer AS GesamtPlannummer FROM Dateien WHERE WL.Extractstring(Dateien.Dateiname,1,".",1) = '%1' )",MyPARA_Qry_Masterplan_Gesamt.sDateiendung) |
| |
| |
| | | |
|
| | |
| |
| Publicado el 10,febrero 2016 - 13:50 |
Strange,
if I use this statement, from the hf controlc centre 20, on one of my testdatabases it works like a charm and gives me 3 out of 80 records
select * from tbl_customers where wl.extractstring(email,2147483651,'@')='gmail.com' |
| |
| |
| | | |
|
| | |
| |
| Publicado el 10,febrero 2016 - 18:02 |
Hi Michael,
If you do a info(fExtension) you get 8.
You will probably be able to use it like this in your WL.fExtractPath(MyFileName,
Regards,
Piet |
| |
| |
| | | |
|
| | |
| |
| Publicado el 11,febrero 2016 - 11:28 |
Hi Piet,
fextractpath(files.filename, runs, but it returns 0 rows ... I think the "8" is not right in the SQL Environment.
I gave up and use "SPLIT_PART" which works but is no 100 percent right if the filename has more than a dot. |
| |
| |
| | | |
|
| | |