.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

SQL Server Equivalent to Oracle INSTR

Posted By:      Posted Date: October 27, 2010    Points: 0   Category :Sql Server

Hi All,

I wanted to know if in SQL Server there is an equivalent to the Oracle INSTR function? I know that there is CHARINDEX and PATINDEX, but with the oracle version I can also specify the Nth appearance of the character(s) I am looking for.

Oracle INSTR: instr( string1, string2 [, start_position [, nth_appearance ] ] )

The CHARINDEX almost gets me there, but I wanted to have it start at the nth_appearance of the character in the string.



View Complete Post

More Related Resource Links

Linq with two databases (oracle and sql server)


i have a problem to bring the data from the oracle (external database) and the sql server together (with similar data). The linq entities are linked to each other. The structure is like different countries with different cities and so on. To display these "non original linq objects" i need to put them into entities (linked to each other). Now i add some of them to my sql database. But for every entity i must check the connection to the other objects i don't want to add and delete them (otherwise linq will add all the connected objects, even those I don't want to add). If this goes wrong the whole input-method crashes. Is there any other possibility, except from checking every single entity and removing unwanted connections, to handle this procedure?
Thanks a lot, preg

No Way to retrieve data from oracle ref:_cursor over T-SQL and linked Server?

Hello, for an migration projekt we want to compare results from sp's from oracle and sqlsserver. Same calls should retrieve same results. Also we want build an automatic test for this. But is there now way to retrieve results from oracle sp's whit rev_cursor over linked Server? No one answer to this: http://social.msdn.microsoft.com/forums/en-us/sqldataaccess/thread/2BAC6743-8701-4476-8F36-0377A5761525   greetings Michael

Server Cache Issue - .Net 2.0 / Oracle 11g / 2003 Server / IIS 6.0

Hi All,We are working on a web application developed in C#, nHibernate and with Oracle as back-end.WebServer and Database are on different machines.Application is more or less accessed by 500 users all the time.Recently we found that some of the controls within MultiView Tab contol were not working as expected. Basically, data is not getting saved.For instance, if there are 5 tabs and in the first tab if we change an option in the dropdownlist, in the autopostback event, the new value should get saved to database. If we navigate to another tab and visit the previous tab, the new option should remain there (reloading the data in the page_load, preRender events).But once we restart the IIS, all this functionality works as expected.Even if there is some database issue, like foreign key violation etc, application is behaving weirdly and once we fix the database issue, we still need to restart IIS to clear cache for the application to behave normally.I am not sure if any of you ever faced this issue but please let me know where to start analyzing this issue?Thanks in advance.ys


hai.......I am a biggner in SQL DB . but i started a complicated and painfull work in SQL SERVER 2008.the problem  convert Oracle hierarchical query to SQL query please help. the query SELECT DISTINCT         LEVEL LVL,         SCH.NSCHEDULE_SL,         SCH.NSCHEDULE_SL_FM,         SCH.CSHED_CNAMEFROM  FA_SCHEDULES SCH WHERE     LEVEL  = 1 AND    NSCHEDULE_SL_FM  IS NULL AND    NBRANCH_SL  = 2  CONNECT BY PRIOR SCH.NSCHEDULE_SL_FM  = SCH.NSCHEDULE_SL AND    NBRANCH_SL  = 2please help me

before insert equivalent in sql server

Is there an equivalent of "Before insert" trigger of Oracle in SQL Server 2005. I tried "Instead of" trigger but It does not actually insert into the main table and executes the statements defined in the trigger. Currently I hav two after insert triggers in sql server both of which are getting called upon insert. I want to differentiate them as I am doing it in ORACLE by defining one of the two as BEFORE INSERT trigger.

SQL server 2008 to oracle replication

Hi, I am trying to replicate data from a sqlserver to an oracle 11 subscriber(push subscription). I recently moved to SQL server 2008 from 2005. The replication in 2005 worked ok, and now in 2008 - it does not. The problem: It tries to make some fields as an interval data type in Oracle. For example, nvarchar(3) are turned into interval and numeric(38,8) fields are turned into nvarchar2(40). Anyone has any idea why is this happening and how can I change it?

Need Information on pulling data from Oracle Source to SQL Server

Hi,   I was told that we will be extracting data from Oracle database and loading into SQL Server Database. I will be developing packages on the Remote Server where SQL Server is installed. I mean I wont be developing anything on my local machine. This is the first time I will be writing a package to extract data from Oracle database and my knowledge on Oracle is zero.   Could someone list out  what all I need to do to extract data from Oracle database ?  Server that I will be working is new and nothing is installed on that except the SQL Server 2008.      

Folders or folder like equivalent in SQL Server 2008 ? for packages & stored procedures ?

Hi,      Currently running SQL-Server 2000.  We have hundreds of stored procedures and DTS packages. We have so many it's becoming a serious challenge for names and such.  We were wondering if SQL Server 2008 has a way to 'nest' both the equivalent of DTS packages (SSIS) and stored procedures in something akin to 'folders'.  We know we have to upgrade one of these days but a version that would handle these would be much more attractive. Just getting this functionality would be worth whatever effort would be required to upgrade.   Thanks in advance for any help on this.

Variable issue - SQL Server to Oracle

i use a variable (date type is Object) to store number from sql server, the format is: 123 456 789,00 (Norwegian) when i execute the package and sync it to Oracle (the input parameter is numeric), then an issue occured, it said can not covert the string to numeric. how to transfer it to the stardant format: 123456789.00?   thanks

Transforing the Data from Oracle to SQL Server 2008

Hi all I have to create a SSIS package which can Transfer the Data from Oracle to SQL Server 2008. Every time when packages run it will suppose to create tables in SQL Server Database. I guess it is possible with export and Import, but the privies tables suppose be deleting before or after dumping.   Suggest me best method to do this.     Thanks in advance     SNIVAS

SSIS from MS SQL Server 05 to Oracle 10g 250 Tables

Hi Guys, I want to write a neat SSIS to automate the monthly load of 250 MS Sql Server Tables to the Oracle 10g database. Now, I know how it works but I dont want to create 250 data export/import steps. Is there a that the SSIS captures the schema, does the data type conversion and dumping automatically? I know Oracle SQL Developer does this whole "schema capturing" but it lacks automation. Thanks, metalray

Server 2008 R2 64 bit, SSRS 2008 64 bit with Report Builder 3.0 issues with Oracle OLE Driver

Folks, ran out of idea on this issues. Setup as shown at the Title. I can connect via VS 2010  but report builder cannot run properly when setup with connection string. I use a 32 bit Oracle Driver and VS 2010 works well with it, however, Report Builder on SSRS report a bad image issues while attempting to use the same driver. What have been tried: 1-Already attempted to try to download Oracle ODP drivers 64 bit, but current driver 11g and minor version above 11g does not install, installation failed. So far all chats indicated that Server 2008 R2 does not have a Oracle 64 bit driver provided y Oracle. Any help is appreciated ....    

Oracle clustered table to MS SQL Server 05 SSIS

Hi Guys, Can the SSIS deal with clustered Oracle tables? Moreoever, what are clustered tables compared to partitioned tables? I cant find much about it. (only sytax how to create them). Thanks, metalray

Importing Data from Oracle 10g to Sql Server 2005 using Linked Server

Hi,   I am using Windows 2003 server and Sqlserver 2005 by the use of Linked server , I made a connection to Oracle 10g after that I am importing records from Oracle to sqlserver 2005. When I made tnsnames.ora in sql machine , it worked fine but when i am using tnsnames file from oracle server then i fiired importing procedure it returns below maintain error :   OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Unspecified error". OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Oracle error occurred, but error message could not be retrieved from Oracle.". Msg 7311, Level 16, State 2, Line 1 Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS". The provider supports the interface, but returns a failure code when it is used.   Please let me know.   Thanks

Oracle 11g R2 64-bit linked server difficulty

We are re-integrating a business unit back into IT support this weekend. They have a Oracle production system that's been upgraded to 11G. The previous linked server worked fine. I'm trying to attach from an XP laptop that has a full blown Sql Server 2005 running. I've seen all the post about "in-process" and it doesn't work. I'm getting either 7303 or 7399 regardless of whether I use the Oracle or Microsoft driver. I can successfully connect and query the database from sql plus with no problem. I can create the linked server fine, it's just when I go to query that it errors. "The test connection to the linked server failed." An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "OraAGS". OLE DB provider "OraOLEDB.Oracle" for linked server "OraAGS" returned message "ORA-12154: TNS:could not resolve the connect identifier specified". (Microsoft SQL Server, Error: 7303 Could this be a problem with a 64-bit server connecting from a 32 bit client?? Thanks in advance..

SSIS Package for Delta Data between Oracle and SQL Server


Hi all

I have BI Server and Tables and Data Populated using SSIS package.Now Data is ready in BI server.Next week Ihave to same excersize to filll the Data(DROP tables,CREATE table and Pump the Data)

Can any body give an idea how to create SSIS package which can pupm the Delta Data Oracle and SQL Server.


Thanks in advance




Does SQL Server has the similar function just like Trace Event 10104 for hash join in Oracle?


My question is  how can SQL server display the  information about the buckets when doing the hash join just like the function of Trace Event 10104 in Oracle.By the way ,In Oracle ,the Event 10104 event dumps hash join statistics .
The buckets message what i needed is similar as the following,that is to say ,it must contains the information about buckets .
01 ### Hash table ### 
02 # NOTE: The calculated number of rows in non-empty buckets may be smaller 
03 # than the true number. 
04 Number of buckets with 0 rows: 16373 
05 Number of buckets with 1 rows: 0 
06 Number of buckets with 2 rows: 0 
07 Number of buckets with 3 rows: 1

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend