Oracle and SQL*Server Apostrophe Problem

Posted on 1999-10-15
Medium Priority
Last Modified: 2012-08-14
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?
Question by:tward

Expert Comment

ID: 2133957
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

Author Comment

ID: 2138426
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!
LVL 43

Expert Comment

ID: 2141672
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.
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.


Expert Comment

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


Expert Comment

ID: 2174920
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

Accepted Solution

cymbolic earned 800 total points
ID: 2310486
You have to bite the bullet and make your program dynamically modify the SQL before you issue it to the database.

Here's another interesting little same type grimble.  SQL Server (T-SQL)delimits dates with single apostrophe, MS Access with pound sign.  

What I have done is to examine the connect string (connect property of connection object) for information about the database type I am connected with, then I have a common routine that all SQL and columns pass through which correctly delimits the columns in each SQL statement based on their type.  Real tough to do if you have inline SQL typed into the program, and no knowldge of the data types for each field.

A more sophisticated method is to use the ODBC driver API calls, and get information on the attributes of the connected DSN, assuming you are using ODBC (that is correctly called, SLOWDBC)

Author Comment

ID: 2312024
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.

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
Planning to migrate your EDB file(s) to a new or an existing Outlook PST file? This video will guide you how to convert EDB file(s) to PST. Besides this, it also describes, how one can easily search any item(s) from multiple folders or mailboxes…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

586 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question