Why not operate on the result instead? Using a substitution.
Thanks for the feedback.
I was hoping to accomplish this in a single SQL statement.
You would use a single SQL statement.
It's just what you do with the result.
Let( theResult = ExecuteSQL( .... ); substitute( theResult; " "; ""))
Okay Bruce, thanks for the example and now I understand your comment of why bother.
In reality, I would be returning several columns/fields (approx 10-15) in a somewhat complex multi-join SELECT statement and would want the replace/substitute to occur only in one column.
Otherwise your suggestion would hit it on the mark.
There IS a SQL function <http://msdn.microsoft.com/en-us/library/ms186862.aspx> that does that:
REPLACE(value, ' ', '')
However, It's unlikely to be valid within ExecuteSQL. Or at least in my testing it would not work.
Jay Sayers wrote:
a somewhat complex multi-join SELECT statement and would want the replace/substitute to occur only in one column.
In that case, create a “space-less” calculated version of the studentName field and include that one in the query. It ain't nice, but it works.
Depending on your data and the anticipated size of your found sets, you might be able to run the ExecuteSQL result of a carefully crafted query through the Evaluate function to obtain the desired result.
- If there are quote chars in your data, this could easily break
- The technique is straighforward, but it can require extra attention to detail to get it working right
As a simplistic example:
The following calc is designed to substitute out space chars in field01, leaving field02 intact.
varSQL = "SELECT 'Substitute( \"' || field01 || '\"; \" \"; \"\" ) & \", \" & \"' || field02 || '\"' from Test";
varQueryResult = ExecuteSQL( varSQL; ""; " & Char( 13 ) & " )
Evaluate( varQueryResult )
Important: The above code breaks if either field contains a quote char in the data.
A small amount of discussion about this technique can be found at the following thread: https://fmdev.filemaker.com/message/122257
Edit: Having just read erolst's comment, I believe that to be a much more sound suggestion.
Edit #2: Not only that, I think I originally got the code wrong above. My sincere apologies. I didn't test with a found set of multiple rows and what I posted was bad. Just now updated it with what I believe is correct.
You can use the FileMaker TrimAll function, in this case: TrimAll ( Name ; 1 ; 3 ).
With existing values, you can use Replace Field Contents > Replace with Calculated Result. For new values set your field to Auto-Enter calculation replaces existing value (with the above funtion).
It seems REPLACE is not standard SQL function
and it is not supported in FM and FM doesn't reserve the word.
Adding calc field may be simple workaround. This is able to be unstored, may not be worse than calculating in SQL.
Yes, REPLACE is a standard SQL function (in some flavors of SQL). Note my reply with the link to MS SQL. Also, in my extensive testing this function does NOT work within ExecuteSQL().
agreed that sometimes we need to make a field (calculated) for use with queries. IF this is going to be an oft-used field, my preference is to make it auto-enter and STORED if at all possible.
steve_ssh also came up with a great example (as always)! and it seems to use the SUBSTITUTE() SQL function <http://msdn.microsoft.com/en-us/library/ee634233.aspx>. I don't believe I tested this function with ExecuteSQL(). or the "Evaluate" makes it a FileMaker function. Can anyone confirm this?
The example I posted is not using the SQL SUBSTITUTE function -- it's using the FileMaker Substitute().
Assuming Jay just needs to format that one single field, I think that creating the additional field in the schema is a better solution.
In other cases, where there are more complex needs for transforming the output, I think the Evaluate technique might be appropriate, but it probably should be modified from what I posted in order to allow for quotes chars in the data without breaking.
Further testing on this. (see my other replies)
I used my example files and created this query:
[ $query = "SELECT 'Substitute(\"' || name || '\";\" \";\"\")', salespersonID
; $result = ExecuteSQL ( $query; " & char(9) & "; " & char(13) & " )
) // NOTE: still must be "evaluated", but the correct characters would appear in the final text, including tabs and returns if desired
Keeping in mind that the text must be evaluated to get the text as needed. It's using FMP Substitute() function, not SQL's SUBSTITUTE(), which I did not test. But since it's using the calc engine to perform the substitute, the use of another field to give that, trimmedName=Substitute(name;" ";""), might just as well be used in the query:
[ $query = "SELECT trimmedName, salespersonID
; $result = ExecuteSQL ( $query; Char(9); Char(13) )
) // NOTE: does NOT need to be evaluated
But I do like the thinking outside the box with the concatenation in the eSQL.
LOL! Steve, I was testing and just replied my findings. I confirmed that you meant FMP's Substitute(). See my example for ways to put the tabs and returns into the text that must be 'evaluated'.
I think one could also “go back to the roots source” and do something like …
ExecuteSQL ( " SELECT nameFirst + nameLast, grade etc )
Jay Sayers wrote:
I want to remove the space between "Steve Jobs" to return "SteveJobs"