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://www.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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy