DapperDan
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.
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.
with a space between the CREATE AND THE FUNCTION
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.
All I am able to do is run SQL SELECT statements.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
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
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
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(stri ng1), ' 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?
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(stri
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?
I think you could simplify the Oracle method somewhat.
case when translate(bText,'~01234567 89','~') is null then to_number(bText) end
case when translate(bText,'~01234567
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.
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.
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.
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.
like this
Open in new window