T-SQL equivalent of To_number

Question

T-SQL equivalent of To_number

dimpythewimpy

Ten Centuries

Points: 1286
More actions
September 20, 2016 at 11:17 am

#317437

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;

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

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.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eric M Russell SSC Guru Points: 125560 More actions · Answer 2

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

dimpythewimpy Ten Centuries Points: 1286 More actions · Answer 3

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;

Alan Burstein SSC Guru Points: 61141 More actions · Answer 4

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

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

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?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2