I am new to writing SQL fucntions and trying to write a scalar function in T-SQL. The function should insert leading zeros into an input field of numbers the length of the input can vary in length, i,e,. 123, 1234567, 9876543210 and so forth. The input field is defined as varchar(13) here is the code that I have written for the function...
I am sure that I am overlooking some simple thing, I admit that I am still learning various aspects of SQL and the Datepart function is one.. I am inputting a date and returning an invalid quarter, here is the code: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION Dbo.FN_Get_Quarter ( -- the parameters for the functi...
I have a complex expression calculating a value from a date that I have to use on multiple date columns. Can I define a temporary local function in my query to avoid copy and pasting this expression. ? like: create MyLocalFunc(@ADate datetime) returns int as begin blablabla end select MyLocalFunc(col1), col2, MyLocalFunc(col3), c...
I want to place this working code within a SQL Statement, OR do I need to perform a UDF. The result set is a one line concatenation, and I want it to be place in every one of the overall result set lines. ---- MAIN QUERY SELECT H.CONNECTION_ID, H.SEQUENTIAL_NO, H.INVOICE_NUMBER, H.INVOICE_DATE, H.LAST_INVOICE_NUMBER, ...
Hi, i'm playing around with building a sql function that will extract numbers from a title, which is what the following code below does. Although, i want to modify this function to parse numbers into sections. For example: Current Data in title field: QW 1 RT 309-23-1 QW 1 RT 29-1 QW 1 RT 750-1 QW RT 750-1 Temp tables created once f...
I have this great function that parses a field into a pivot table consisting of four columns so I can sort my table accordingly. My only problem now is I can't use the function in a query that also calls a "text" datatype to be displayed in the results. Query runs fine as long as I don't include "spName" which is a "text" datatype. I've ...
With Delphi 2006, I was using a library of User Defined Functions that was based on tbudf.pas and some functions that I added on my own. Since I am working on a payroll program, this functionality is very helpful for some common payroll concepts that might otherwise require some complex SQL. I have kept my Delphi 2006 installation and ...
I'm trying to create a user defined function in Oracle that will return a DATE when given a text argument containing a date substring. I've tried a couple ways of writing this, and all seem to throw the same error: CREATE OR REPLACE FUNCTION lm_date_convert (lm_date_in IN VARCHAR2(50)) RETURN DATE DETERMINISTIC IS BEGIN RETURN(TO_D...
Trying to put a function call that returns an int into a call for a stored procedure. Getting "Incorrect syntax near '.'." usp_Document_ReserveForGrader '98832750-142F-4623-91F7-6E43C6F1A963', 12,dbo.Workflow_FirstProgress() ...
ColdFusion 8 This is as simple pseudo-code as there can be: <cffunction name="badJSON" access="remote" output="true" returntype="string" returnformat="JSON"> <cfreturn "06762" /> </cffunction> Results in: 6762.0 It should result in: 06762 Is there a way to get JSON to not convert my value to numeric? I know I can use WDDX,...
Problem: I've got a table which holds certain records. After the insert has been done, I want to call an external program (php script) via MySQL's sys_* UDFs. Now, the issue - the trigger I have passes the ID of the record to the script. When I try to pull the data out via the script, I get 0 rows. During my own testing, I came to a conc...
I have a long spreadsheet with tariff codes that I need to validate and I would like to create a function with RegEx to do it automatically (this is a daily task that I will have to do for the following months and I would like to automatize) For example in Column A, I have the following data: CODE 1000.00.00 1000.10.00 1020.12.99 ... ...
Hi, This one is pretty weird ... I have a VSTO Excel 2007 project for C# where I create a bunch of worksheet functions that return object[,]. They all work fine but I wanted to return exception messages/stack traces when the function fails. When I tried this I got #VALUE!'s everywhere. So during troubleshooting the issue I created a s...
Before delving into the issue, first I will explain the situation. I have two tables such as the following: USERS TABLE user_id username firstName lastName GROUPS TABLE user_id group_id I want to retrieve all users who's first name is LIKE '%foo%' and who is a part of a group with group_id = 'givengid' So, the query would like somet...
I am trying to create a custom vb.net Excel 2007 function (UDF) using VS 2010 and have gotten to this stage (borrowing heavily from Eric Carter's example at http://blogs.msdn.com/b/eric_carter/archive/2004/12/01/273127.aspx): Namespace AutomationAddin <Guid("1aeeb1b5-e099-4f7f-aeb0-3e9f19b64f62")> <ClassInterface(ClassInterfaceT...
I am currently running too many sites on a server and I don't think the template cache can handle it. But, what really seems to be the biggest drag is when I load my UDF library per site. I say this because whenever I run Fusion Reactor to see where the holdup is, the stacktrace is always sitting on the template that loads the UDF's. ...
Hi, I have a UDF that does most of the joins and when I run it, it read about 100-150 pages (from sql profiler) but if I wrap this UDF in sp then the sp will do the read about 243287 pages. Furthermore, the UDF itself performs index seek but the sp performs index scan on one of the searched columns. Can anyone please advise? ...
Hi all, Which is the best way to check if 1 item or more of one delimited string exist in another? I'd like to pass 2 delimited strings to a UDF and get back TRUE if at least one item of the first delimited string exists in the second delimited string. delStr1="US,FR,DE" delStr2="GB,DE,CA,IT,PL" delStr3="CN,DK" CheckDelimitedStrin...
I'm about to implement this function to calculate some numbers. CREATE FUNCTION [dbo].[funLookupFTE] (@PFID int) RETURNS VARCHAR(20) AS BEGIN DECLARE @NumberOfFTE AS VARCHAR(20) SET @NumberOfFTE = (SELECT SUM(CASE WHEN Hours <= 20 THEN 0.5 WHEN Hours > 20 THEN 1 END) AS FTECount FROM tblPractitioners ...
When creating a user defined function is it "bad" to just automatically use the largest string possible? For example, given the following UDF, I've used nvarchar(max) for my input string, where I know perfectly well that the function currently isn't going to need to accept nvarchar(max) and maybe I'm just forward thinking too much, but ...