September 20, 2016 at 11:17 am
I have a function in oracle and I am trying to re-write the same in sql server. I tried to rewrite it using cast, but didn't work. I am stuck. Can someone please suggest a workaround. Here's the oracle code and I have to convert to sql server:
FUNCTION IsNumeric (sRESPONSE_STRING IN FY_ST_ANSWER_DETAIL.RESPONSE_STRING%TYPE Default NULL)
RETURN BOOLEAN
IS
test_value NUMERIC;
BEGIN
test_value := To_Number(Replace(Replace(sRESPONSE_STRING,','),'$'));
RETURN (true);
EXCEPTION
WHEN OTHERS THEN
RETURN (false);
END;
September 20, 2016 at 11:28 am
You have an ISNUMERIC function in T-SQL. That can be misleading, but it works: http://qa.sqlservercentral.com/articles/ISNUMERIC()/71512/
Instead of using To_Number, To_Char & To_Date, T-SQL has CAST and CONVERT. You can also use TRY_CAST, TRY_CONVERT and TRY_PARSE to handle errors. I'll leave the research on how these functions work to you.
September 20, 2016 at 11:47 am
Provide examples of the most problematic values you're trying to convert.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
September 20, 2016 at 11:59 am
Here's the code I have. Can someone please suggest an improvement. Is it syntactically correct?
CREATE FUNCTION [HSIP].[Isnumeric]
(
@sResponse_string varchar(250)
)
RETURNS BOOLEAN
AS
BEGIN
RETURN
DECLARE @Output AS INT
select @Output= CASE @sResponse_string
WHEN @sResponse_string= ISNUMERIC(REPLACE(REPLACE(@sResponse_string, ',',''),'$','')) THEN 1;
ELSE 0;
September 20, 2016 at 12:27 pm
dimpythewimpy (9/20/2016)
Here's the code I have. Can someone please suggest an improvement. Is it syntactically correct?CREATE FUNCTION [HSIP].[Isnumeric]
(
@sResponse_string varchar(250)
)
RETURNS BOOLEAN
AS
BEGIN
RETURN
DECLARE @Output AS INT
select @Output= CASE @sResponse_string
WHEN @sResponse_string= ISNUMERIC(REPLACE(REPLACE(@sResponse_string, ',',''),'$','')) THEN 1;
ELSE 0;
There is a lot wrong your query. This works though:
CREATE FUNCTION dbo.Isnumeric2 -- can't name the function ISNUMERIC because that name is already in use
(
@sResponse_string varchar(250)
)
RETURNS /*BOOLEAN*/ BIT -- no booleans in T-SQL
AS
BEGIN
RETURN ISNUMERIC(REPLACE(REPLACE(@sResponse_string, ',',''),'$','')) -- note: ISNUMERIC returns a 1, 0 or NULL (for NULL Inputs)
END
GO
Why would you need this user defined scalar function (scalar UDF) though? You could just do this:
ISNUMERIC(REPLACE(REPLACE(@sResponse_string, ',',''),'$',''))
Putting this logic into a scalar UDF will slow things down, scalar UDFs always slow things down. I strongly suggest not using a scalar udf.
On a separate note (and this is a little advanced but it's career changing advice IMHO) - if you MUST put logic like this in a function, use an inline table valued function (ITVF). You can use an iTVF to return a single value and will do so much faster and more efficiently than a scalar UDF. See this article for more details.
The iTVF version would look like this:
CREATE FUNCTION dbo.itvfIsnumeric
(
@sResponse_string varchar(250)
)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT [ISNUMERIC_SPECIAL] = ISNUMERIC(REPLACE(REPLACE(@sResponse_string, ',',''),'$',''))
GO
Using it is a little different, see below:
SELECT dbo.Isnumeric2('123.444$$');
SELECT * FROM dbo.itvfIsnumeric('$123.555');
... and against a table:
CREATE TABLE #tmp(col1 varchar(250));
INSERT #tmp VALUES ('123'),('$100'),('100,104,555.005'),('xxx');
-- scalar version
SELECT col1, dbo.Isnumeric2(col1)
FROM #tmp;
-- itvf version
SELECT col1, [ISNUMERIC_SPECIAL]
FROM #tmp
CROSS APPLY dbo.itvfIsnumeric(col1);
edit: typos
-- Itzik Ben-Gan 2001
September 20, 2016 at 12:36 pm
It's not syntactically correct.
SQL Server does not have a boolean data type, it only has a bit data type.
Your BEGIN has no END.
You're RETURN does not have anything to RETURN.
CASE is an expression and it's part of a STATEMENT, you have the syntax completely wrong. Check both possible syntaxes here: https://msdn.microsoft.com/en-us/library/ms181765.aspx
You're also doing unnecessary work (try this: SELECT ISNUMERIC('$15,322.12')) and asking for performance problems. Scalar user defined functions are slow in SQL Server and can make a query run at least twice as slow (probably 10 or more times slower).
Why don't you stay with the system function?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply