asked on

Oracle and SQL*Server Apostrophe Problem

I have a program that was geared towards Oracle.

This program has many select statements that use Apostrophes around number fields which works fine for Oracle, but SQL*Server does not like this..

Is there a setting in SQL*Server that will allow Apostrophes to be around numbers as well as strings?

simonsabin

No, I am afraid with out changing you tables, you are stuffed. The only other option is to create a view qhich converts the specific field to text, you would however then not be able to use indexes

tward

ASKER

I'm going to leave it out for other comments/answers. I have started changing the program but for future case I would like to see if there is any other way!

TimCottee

SQL Server does not allow implicit conversion of datatypes in most cases. Text / character data cannot be implicitly converted to int/real/binary etc. You could use the convert function e.g.,

select x,y,z from xyz where x = convert(int,'nnn')

I don't know whether this is supported under Oracle.

tbcox

Can you supply an example statement? I'm not really following what the problem is.

-Tom

simonsabin

The problem is Oracle allows implicit conversion between numbers and strings i.e
1='1' is true in Oracle but not supported in Sql server

ASKER CERTIFIED SOLUTION

cymbolic

membership

This solution is only available to members.

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

Start Free Trial

tward

ASKER

That is basically what I had to do.

When I am building the SQL Statement, for each field I check the type to see if it needs apostrophes or not.