asked on

String to Integer in SQL Server / Oracle / DB2 SQL

Hello there

I have an issue creating a join between 2 particular tables in a 3rd party database. This database can run on SQL Server, Oracle and DB2.

Table A contains the numeric field aNum.
Table B contains the string field bText.

For the records I wish to join bText contains the value from aNum, converted to a string. However, bText can also be empty or have any other alphanumeric characters.

I want to be able to create a join that says:
aNum = TO_INT(bText)

I have tried various CAST and CONVERT functions on different platforms. All the ones I have tried throw an error when they find a non-numeric character in bText.

I know there are other ways of approcahing this. My current solution is to create a condition like this:
TO_STRING(aNum) = bText

While this works, the performance suffers badly. aNum is indexed and bText is not. The client is reluctant to add an index onto bText.

I am offering 500 points for this.

My ideal solution would be a SQL function that converts text to a numeric, but just returns null if it cannot be converted, throwing no errors.

It can be a single function, or combination of functions, but they must all be standard built-in functions. I cannot add stored procedures onto client machines.

I need a solution that will work on SQL Server, Oracle and DB2, or 3 seperate solutions. It is fine if they all use different functions, as long as they are all standard to that platform.

Alternatively, I will accept any other solution that allows the join to make use of the index on the aNum field.

Dimitris

In sql you can create a function that is called TO_INT

like this

CREATEFUNCTION dbo.TO_INT(@StringIn nvarchar(4000))
RETURNS INT 
as
BEGIN
	DECLARE @tmpRV int
	SET @tmpRV = null
	IF ISNUMERIC(@StringIn)=1
		SET @tmpRV = CAST(@StringIn as int)
	
	RETURN @tmpRV
END

Open in new window

Dimitris

with a space between the CREATE AND THE FUNCTION

DapperDan

ASKER

Thanks, but I am not able to create functions, as I do not have user permissions for that on the client machine.

All I am able to do is run SQL SELECT statements.

ASKER CERTIFIED SOLUTION

Dimitris

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DapperDan

ASKER

I don't think it can work exactly as you describe. The problem is that as soon as it finds a non numeric string it will throw an error and stop the query

However, I think you may be onto something. If I can get it to only execute the CAST if ISNUMERIC returns true, that should sort it out.

I'm thinking something like this

aNum = CASE WHEN isNumeric(bText) THEN cast(bText as int END CASE

I will have a play with it this afternoon.

Cheers

DapperDan

ASKER

This IsNumeric function works like a charm on SQL Server

aNum = CASE WHEN isNumeric(bText) THEN cast(bText as int) END CASE

Sadly there is no built-in IsNumeric function for Oracle or DB2.

There is an ugly method that works on Oracle, using Translate to strip out the non-numeric characters and measuring the length. Something like this:

LENGTH(TRANSLATE(TRIM(string1), ' 0123456789', ' ')))

Ugly, but I could live with it.

I haven't been able to find a method for DB2 at all. Anyone have a method for DB2?

awking00

I think you could simplify the Oracle method somewhat.
case when translate(bText,'~0123456789','~') is null then to_number(bText) end

DapperDan

ASKER

Thanks Awking

You may well be right. I can play about with that later.

What I really need now is a solution for DB2. I'll leave this issue open for another couple of days.

DapperDan

ASKER

Thanks, don't think anyone else will comment now.
This got me part of the way there. Haven't found a solution for DB2 yet, so I may post a new question in the DB2 section.