Oracle Translate Function Equivalent In SQL Server
Hi
I want to know the equivalent of the Oracle translate function in SQL Server.
eg : select translate('entertain', 'et', 'ab') from dual.
I tried the SQL Server Replace function , but it replaces only
one character or a sequence of character and not each occurrence of
each of the specified characters given in the second argument i.e 'et'.
Please let me know if there is some other equivalent function in SQL Server
thanks.
View Complete Forum Thread with Replies
Related Forum Messages:
Oracle Translate Function Equivalent In SQL Server
Hi I want to know the equivalent of the Oracle translate function in SQL Server. eg : select translate('entertain', 'et', 'ab') from dual. I tried the SQL Server Replace function , but it replaces only one character or a sequence of character and not each occurrence of each of the specified characters given in the second argument i.e 'et'. Please let me know if there is some other equivalent function in SQL Server thanks.
View Replies !
Translate Equivalent In Sql Sever
Hi, I have an alphanumeic string as one of the column values.I want to get numeic value out of the string. I was planning on translating all the characters of the alphanumeric sting to spaces and then using ltrim and rtrim on it should give me the required numeric sring.But there is no translate function in sql server Example: abcd123xy 123 xyz45 45 Can someone provide me a way to get the numeric value out of the strng. Thanks Bobby
View Replies !
Outer Join Oracle Query Translate In Sql Server
Hi, I have this oracle query with outer join situation. how can i convert it into sql server query. SELECT distinct ae.dB_CONTRACT,CP.PC_CODE,BID_ITEM.ITEM_NO, 'N',BID_ITEM.PRICE_WORDS,OFF_ITEM.DESCPT, OFF_ITEM.UNITS,OFF_ITEM.TYPE_ITEM, PRES_ITEM.RET_PERC FROM BID_TOTAL,BID_ITEM,OFF_ITEM,PRES_ITEM, AE_CONTRACT AE, CONTRACT_PC CP WHERE RANK_NUMB = 1 AND BID_TOTAL.DB_CONTRACT = 37044 AND BID_TOTAL.DB_CONTRACT = BID_ITEM.DB_CONTRACT AND BID_TOTAL.BID_VENDOR = BID_ITEM.BID_VENDOR AND BID_ITEM.DB_CONTRACT = OFF_ITEM.DB_CONTRACT AND BID_ITEM.ITEM_NO = OFF_ITEM.ITEM_NO AND OFF_ITEM.ITEM_NO = PRES_ITEM.ITEM_NO (+) AND AE.DB_CONTRACT=BID_TOTAL.DB_cONTRACT AND CP.DB_CONTRACT = AE.DB_CONTRACT AND CP.pc_code = 1 Any Help will be appreciated.
View Replies !
How To Translate Oracle Decode
Hi, How to translate oracle Decode without changed code I mean: I have one application and instead to change all decode to case when I would like just replace decode for dbo.decode, so I Wrote this function select dbo.fnDecode( 1 , 2 , 3 ) first parameter always int, and the others parameters could be char, int or float. I would like to test first value and return second or third value -> sql_variant for all parameters, ok but I can use + or - I can't do this select dbo.fnDecode( 1 , 2 , 3 ) + dbo.fnDecode( 1 , 2 , 3 ) If I put cast ok, but I is there another way, overload this call? With clr doesn't work, because Sql Server doesn't accept function overload calls from C# Any ideia???????????????????? cheers,
View Replies !
MS-SQL Server Equivalent To Oracle 9i?
All,Oracle 9i provides a "USING" clause option for inner joins, thatallows me to say:SELECT * FROM TBL1 JOIN TBL2 USING KeyColumnassuming KeyColumn is in both TBL1 and TBL2. This is HIGHLY desirablefor our software make use of, but we also support SQL Server. Thereis no USING option available, andSELECT * FROM TBL1 JOIN TBL2 ON TBL1.KeyColumn = TBL2.KeyColumncauses an ambiguous column error on KeyColumn.Is there any equivalent to this Oracle functionality on SQL Server?KingGreg
View Replies !
Translate Function?
Hi allIs there a TSQL function like Oracle's Translate function? Where:Translate('13,000Miles','abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOP QRSTUVWXYZ, ',' ') wouldreturn '13000'?I'm trying to extract a number from a string. REPLACE doesn't quite dowhat I want.Thanks
View Replies !
Translate Function
Hi folks! Since Transact SQL does not have a Translate function, does anyone have a snippet of SQL code that translates a particular ASCII character (e.g., carriage return CHAR(13)) to another ASCII character (e.g., space). I`ve tried using the Stuff function, but it doesn`t seem to work very well for something that is the last character in a VARCHAR column. Any help would be appreciated. Thanks, Wayne
View Replies !
Translate Function
Can i use Translate function in sql server query? How i ll use? In my oracle, i m using a Translate function, but i want to run that in sql server. How to do that?
View Replies !
ORACLE BEFORE INSERT EQUIVALENT IN SQL SERVER
I am having trouble creating an INSTEAD OF trigger in SQL Server toreplicate a BEFORE UPDATE trigger from ORACLE.Here is a sample of the ORACLE BEFORE UPDATE trigger:CREATE TRIGGER myTRIGGER ON MYTABLEbegin:new.DT := SYSDATE;if :new.NM is NULL then:new.NM := USER;end if;end myTRIGGER;It seems as though I have to jump through hoops in SQL Server AND Icannot come up with correct results.Here is a snippet from SQL SERVER (this is what I figured I needed todo after reading various articles,questions):CREATE TRIGGER myTRIGGER on THETABLEINSTEAD OF UPDATEASSELECT * INTO #MYTABLE FROM INSERTEDUPDATE #MYTABLE SET DT = GETDATE()UPDATE #MYTABLE SET NM = USER WHERE NM IS NULLUPDATE THETABLESETDT = (SELECT DT FROM #MYTABLE),NM = (SELECT NM FROM #MYTABLE)WHERE THETABLE.ID = (SELECT ID FROM #MYTABLE)Can anyone please shed some light on this? Thanks in advance.
View Replies !
Is There A SQL Server Equivalent For Oracle Synonyms?
I have a SQL Server database which has one user (UserA) which owns some tables. I've added an additional user (UserB) to the database such that it has access to the tables owned by UserA. What is happening is that when I log on as UserB I have to fully qualify table names and fields in my SQL statements when I deal with tables owned by UserA. Is there a way make the tables accessible without specifying the owner? In Oracle you could create a public synonym for the table eg. <table_name>. Wherever that synonym is referenced the DBMS would know thats its refering to UserA.<table_name>. Is such functionality available in SQL Server? Thanks.
View Replies !
Oracle's ROWNUM Equivalent In SQL Server
Hi, Can any one tell me is there anything in SQL Server thats equivalent to Oracle's ROWNUM. Note that the Identity Property or TOP n will not solve my problem. I want to asign a sequence no. to each row when its being fetched. For example if in the emp table there are 2000 rows and I write the following query in Oracle , SELECT rownum , empno, empname FROM emp Where rownum < =3 I get the result like this Rownum----Empno--------------Empname ------------------------------------------ 1-----------2345---------------ABCD 2-----------3334---------------EFGH 3-----------4484---------------IJKL I know I can limit the output rows in SQL Server by using TOP n. But I also want to generate a sequence no. The identity property of SQL Server will not be usefull here because my actaul WHERE clause will be more complex like WHERE resigndate = '01-jan-2004' Thanks Asim Naveed 3
View Replies !
Is There An Equivalent Of Oracle's SQLERRM In SQL Server 7?
Hi all, here is my dilema. I am converting an Oracle database into SQL Server 7. In Oracle, when I receive an error message I capture it by sticking it into a variable. Example: v_ErrorText := substr(sqlerrm, 1, 200); I stick that error text into a separate table called TERRORLOG which has the name of the user, date, and my own description of the error as well. I have not found a way to do this in SQL Server. So far I have been using @@ERROR but this only gives me the error number to put into my table. How can I also get the system messages associated with this error? I do not want to go to the master.dbo.sysmessages table every time to look up the error. I would like to have that error text placed into my table with the error number. Any help is appreciated! :)
View Replies !
Equivalent Of Oracle's INSTR( With 4 Parameters) In SQL Server
The syntax for Oracle's INSTR function is instr (string1, string2, [start_position], [nth_appearance]) string1 is the string to search. string2 is the substring to search for in string1. start_position is the position in string1 where the search will start. This argument is optional. If omitted, it defaults to 1. The first position in the string is 1. If the start_position is negative, the function counts back start_position number of characters from the end of string1 and then searches towards the beginning of string1. nth_appearance is the nth appearance of string2. This is optional. If omiited, it defaults to 1. In SQL Server, we are having CHARINDEX and PATINDEX functions. But they will not accept the fourth paremeter (nth_appearance) Do anybody know the solution for this ????
View Replies !
How To Convert Pl/sql Code Of Oracle To Something Equivalent Which Works On Sql Server 7.0?
i had worked on oracle 8i and i am planning to work on sql server 2000,i am requested by a company to help in converting there pl/sql code of oracle 8.0 to something equivalent which works on sql server 7.0 as they want to have similar code on both..i had not worked on sql server 7.0 ,but as pl/sql code works only on oracle stuff..so could kindly anyone guide me in this as to whether there is any product which coverts pl/code (the existing pl/code runs into thousands of line) automatically..i will be very grateful if anyone can enlighten me with such a product(software) or script.. along with its information and site address..any resources and any guidance as to how to go about about this conversion will be very invaluable..hope to hear soon from you guys...early response....will be appreciated.. with regards, vijay. sql server 7.0 on winnt pl/sql code on oracle 8.0
View Replies !
What Is Equivalent Of Format(date) Function Of MS Access In MS Sql Server 2000
Hi All,I am facing a problem with a sql what i used in MS Access but its notreturning the same result in MS Sql Server 2000. Here i am giving thesql:SELECT TOP 3 format( MY_DATE, "dddd mm, yyyy" ) FROM MY_TAB WHEREMY_ID=1The above sql in ACCESS return me the date in below format in onecolumn:Friday 09, 2003But in Sql server 2000 i am not getting the same format eventhough iam using convert function, date part function etc.Please if you find the solution would be helpful for me..ThanksHoque
View Replies !
Ms Sql Equivalent Of This Oracle
Hi.I'm a casual sql user. I have found a situation where I need to convert anoracle statement to tsql, one I can just fire off in any sql tool against anms sql server database.I studied the exists statement and I think I understand it somewhat, however Iwas not sure how to get it quite right. If you have an idea and a minute ortwo I'd appreciate any insight or tutorial.insert into authorization (program, optiontitle, usergroup, authorizationid)select 'EVERYWHERE','NAVIGATOR',usergroup, authorizationseq.nextvalfrom allgroups where exists (select * from authorizationwhere authorization.USERGROUP = allgroups.USERGROUP andauthorization.optiontitle = 'READ' and authorization.program = 'EVERYWHERE')I believe that because in my data, three values of usergroup from allgroupsreturn true from the exists, that this is supposed to insert three rows intoauthorization.But I can't figure out what to do about the authorization.nextval.. I triedvarious max(authorization)+1etc but nothing seemed to compile/workthanksJeff Kish
View Replies !
Any Equivalent For NEXT_DAY Fn Of Oracle
Haii Friends, Is there any equivalent for Oracle's NEXT_DAY function in sqlserver.I need it for the following query... SELECT NEXT_DAY( SYSDATE ,'THURSDAY') FROM DUAL; ur Help in this regard is really appreciated.... Regards, Vicky
View Replies !
Equivalent For A Oracle Query
I need to know the SQL Server equivalent for the below Oracle query : select t.* from table(cast(c1.table1 as create_type_of_table)) The above query will be used in PL/SQLs in ORACLE. What is the SQL Server equivalent for the above query ?
View Replies !
Equivalent For A Oracle Query
I need to know the SQL Server equivalent for the below Oracle query : select alias1.* from table(cast(c1.table1 as create_type_of_table)) alias1 The above query will be used in PL/SQLs in ORACLE. What is the SQL Server equivalent for the above query ?
View Replies !
Oracle Rowid Equivalent In MS SQL
Hi all, I'ld like to get the last record inserted into my DB. In oracle I use: select * from <tabella> where rowid = (select max(rowid) from <tabella>. It is an equivalent of rowid in MS SQL? Thanx TT
View Replies !
Equivalent Oracle Rownum In SQLServer
Hello,I would like to know if the equivalent Oracle rownum exist inSQLServer. Here is a sample SQL code to explain what I want to do :selectjobs.name,jobs.job_id,jobs.description,hist.message,hist.step_name,hist.step_id,hist.run_status,hist.run_date,hist.run_time,hist.run_durationfrommsdb.dbo.sysjobs jobs,msdb.dbo.sysjobhistory histwherejobs.job_id=hist.job_idand hist.job_id='E71CCB97-81C3-46E2-83FA-BFFCB66B47F8'order byrun_date, run_timeI just want the first or second row returned by this query. In Oracle Ican simply add rownum=1 or rownum=2 in the where clause to obtain thedesired answer. I don't know how to do in SQLServer.Thank in advance,Pierig.
View Replies !
SQLServer Equivalent For Oracle Query.
Hi, Could anyone tell me what is the MSSQLServer equivalent of the following Oracle query?. SELECT v1.cat, v1.gnr, TA1.desccde, v1.type, v1.cde FROM view1 v1, table1 TA, table1 TA1, table1 TA2, table1 TA3 WHERE TA.txtcde (+) = TRIM(v1.cat) ANDTA1.txtcde (+) = TRIM(v1.gnr) ANDTA2.txtcde (+) = v1.cde ANDTA3.txtcde (+) = TRIM(v1.type) Thanks, Gopi. Follow your DREAMS...
View Replies !
Equivalent In Oracle, Needed Urgently
CREATE TABLE [omx].[UserAuthorization]( [MemberAuthorizationRecordIdentifier] [varchar](50) NOT NULL, CONSTRAINT [PK_UserAuthorization] PRIMARY KEY CLUSTERED( [MemberAuthorizationRecordIdentifier] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]
View Replies !
Equivalent Of UTL_FILE Of Oracle In MSSQL
Hi all, I am currently migrating my database from oracle to MSSQL. My problem is that i have encountered the UTL_FILE function in Oracle and i am not finding its equivalent for MSSQL. Can anyone help me with this plz? Is there any other way of reading and writting OS files in MSSQL? Regards Dhiraj Software Engineer.
View Replies !
Does MS SQL Have An Equivalent Function...?
...For Oracles DECODE function? I am trying to get a conditional output RETURNed to the Grid output and have not found it in SQL Help. Help!!! Here is Oracles example: Select Distinct City, DECODE (City, 'Cincinnati', 'Queen City', 'New York', 'Big Apple', 'Chicago', 'City of Broad Shoulders', City) AS Nickname From Cities;
View Replies !
Equivalent Of VB MsgBox Function?
Hi, I'd like to perform an update on a database only when certain conditions are met. Hence, one of those conditions would be a positive answer from the user running the query. Basically, I'd like to display a message with a yes/no option for the user to choose from. If and only if the user user selects the YES option should we perform the update. Inside a transact SQL "IF ... THEN", how can I achieve this? Thanks, SC
View Replies !
CLR Function Equivalent In SQL 2000
Hi, I have successfully created a CLR function (C#) in SQL 2005 and call it from a stored procedure. I need to be able to provide the same functionality for SQL 2000 servers. Is this at all possible? I have read loads of conflicting information on the web about using COM to access the dll €“ can it be done or not? The function accepts serveral input parameters and internally access the data store using a context connection and a datareader. Thanks.
View Replies !
Loading Sql Files From A Master File Oracle Equivalent
Hi.I need to give my customer an sql file that they can run in query analyzer.All the stuff they need to run is in a set of existing files.I'd like to just tell them to load this file (this is oracle syntax):@file1.sql@file2.sql@file3.sqlis there some way of calling these files (that are in the same dir) from amaster sql file?ThanksJeff Kish
View Replies !
Informix Date Function Equivalent
Hello. I have an Informix SQL statement that I need to run in MS SQLServer. When I try to execute it I get the following error message fromQuery Analyzer:Server: Msg 195, Level 15, State 10, Line 4'date' is not a recognized function name.Can anyone help me convert this informix sql statement into and MS SqlStatement? Here is the query I have:selecta.comp_code,a.comp_date,case when date(date(date(comp_date - day(comp_date) +1) - 2 unitsmonth) - 1 units day) < b.inception_date then b.inception_dateelse date(date(date(comp_date - day(comp_date) +1) - 2 units month) - 1units day)end prior_date,a.net_return,a.net_uv,a.gross_return,a.gross_uv,a.estimated_flagfrom composite_perf a, composite_detail bwhere(month(a.comp_date) in (3,6,9,12) or a.comp_date = b.inception_date)and a.comp_code = 'AEU'and a.comp_code = b.comp_codeinto #tmp_composite_dataThanks in advance.
View Replies !
Transact SQL - Does It Have The Equivalent Of FINDONEOF Function?
Hi All, Once again I find myself smacking my head into a wall (and it will probably be for naught). I'm trying to find a specific character in a string within a specific record. I've tried substring, but it's not quite getting me where I want to be. I even tried "nesting" the substring and got nowhere (but I could have had the syntax wrong when I nested it). Here is a sample Select substring(productimagefile, 20, 5) from my table What I need is something like this: Select substring(findoneof(productimagefile, _r, 1), 1, 5) from my table Actually, on the start of the substring, I need it to be where it finds the _r, just not sure how to tell it that. The replace function is the closest I've seen to what I need, but I don't want to replace it with anything, I just need to find it in the string. So, if anyone has any ideas, I'd be grateful (and my head would stop hurting....) Schimelcat
View Replies !
What Is The SQLCMD Equivalent Of Oracle &&"Set Echo On&&"
I know you can start SQLCMD with the -e option to have the SQL code echoed in the output when running. Is there a way to turn this on or off within a script file? Example: File1.sql = select 'hi' as HI go File2.sql = select 'by' as BY_ go Runfile.sql = set (like echo on, -e sign on parm) ? what command :r File1.sql set (like echo off, -e sign on parm not used) ? what command :r File2.sql want to get output like select 'hi' as HI (echoed the select statement) HI -- hi (1 rows affected) BY_ ( did not echo the select statment) --- by
View Replies !
&&"IN&&" Function Equivalent For Expressions?
Trying to write the below in an expression box, I still have not figured out an equivalent to €śIN€? in SQL syntax, so far I€™ve had to use €śOR€? to get it to work. Anyone know the right way? I€™m trying to avoid writing this in the stored proc Example: Iif (Fields!INVESTOR_NBR.Value in (0, 265, 465), "DIRECT", "PARTICIPATIONS")
View Replies !
Oracle NVL Function
Hi, I want to know if there is a function in SQL Server that do the same thing than the Oracle function NVL() In Oracle we can do the following thing : select nvl(last_name,first_name) from table_test; This have the effect that if the last_name field is NULL, it will display the first_name Is there a function like this in SQL 7 Thank Martin
View Replies !
Equivalent To MySQL's Password() Function? (was &"MySQL To SQL Server And Password()&")
I have an internal Project Management and Scheduling app that I wrote internally for my company. It was written to use MySQL running on a Debian server, but I am going to move it to SQL Server 2000 and integrate it with our Accounting software. The part I am having trouble with is the user login portion. I previously used this: PHP Code: $sql = "SELECT * FROM users WHERE username = "$username" AND user_password = password("$password")"; Apparently the password() function is not available when accessing SQL Server via ODBC. Is there an equivalent function I could use isntead so the passwords arent plaintext in the database? I only have 15 people using the system so a blank pwd reset wouldn't be too much trouble.
View Replies !
How To Use The OLEDB Command To Call A Oracle Function?
HI, I want to use the OLEDB command to call a oracle function, but i havnt found any materials about how to do that, my oracle function is as below: CREATE OR REPLACE function GET_ZONEID_FROM_SYFZ(ycz varchar2,xc varchar2,strat_id varchar2) return varchar2 IS zone_id_result varchar2(10) ; begin PKG_DM_DQ.GET_ZONEID_FROM_SYFZ(ycz,xc,strat_id,zone_id_result); return zone_id_result; end; In OLEDB command transformation component, i fill the sql command with "select GET_ZONEID_FROM_SYFZ(?,?,?) from dual", but i dont have it worked. The error message is :provider can not derive parameter information and setparameterinfo has not been called. Who have any idea about how to make it work? Thanks ~~
View Replies !
Timeout Error While Executing Oracle Function
Hi, I have developed reports for Oracle database. I wanted to avoid use of OUT REF cursor hence am using function & calling it from reports. The logic in SP/function is so complex that it takes 5 mins to execute n get the result. I see the results on the report server, But when I call that report from web application, I get System.Net.WebException - Time out error. My questions: 1. What setting I make on report server to avoid timeout? --Are these settings applied on report server restart? 2. Is there any setting needed for Datasource also? TIA Tanmaya
View Replies !
Can I Use &"translate&" In Sql Server,is It A Keyword
im trying to find the equivalent of the below oracle code in sql server.But to my knowledge i feel "translate "is not supported by sql server. so please send me the equivalent sql server query for this.SELECT 'http://ploft.com/ploft?y='||translate(comp,' ','+')||',+'||translate(california,'','+')||',+'||ca||'+'||pzINTO :ls_MAP FROM project WHERE portno=:dbno;
View Replies !
How To Translate Into SSIS??
Hi everyone, My current dutie is translate Vb code into SSIS. The following structure is getting me totally crazy: sql = "SELECT * FROM TABLE1 WHERE FIELD = XXXX" rs.execute sql WHILE NOT RS.EOF STUFF ANOTHER SELECT AND ANOTHER LOOP ... ... INSERT AND DELETE STUFF END WHILE Which is the best method in order to reach this goal? I'm trying to by means of OLEDB Source Editor connected to Script Component Task on data flow layer but I'm stuck with this. I don't want to do cursors or something like that with T-SQL. Thanks in advance for your inputs/help and regards,
View Replies !
Trouble With: Linked Server To Oracle Using OraOLEDB.ORacle Provider
Hi-- I am running SQL Server 2005 on Win2k3: Microsoft SQL Server Management Studio 9.00.2047.00 Microsoft Analysis Services Client Tools 2005.090.2047.00 Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324-1447) Microsoft MSXML 2.6 3.0 4.0 6.0 Microsoft Internet Explorer 6.0.3790.1830 Microsoft .NET Framework 2.0.50727.42 Operating System 5.2.3790 I have the OraOLEDB.Oracle provider installed to the (C:oraclexe) directory. I am having problems querying from linked oracle server. When i setup oracle as a linked server and purposely enter an incorrect password the query i run tells me i have an incorrect password. So it at least knows that. when i set the correct password and run a query I get this error: (i replaced the real server name with "someServer".) Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "OraOLEDB.Oracle" for linked server "SomeServer" reported an error. The provider did not give any information about the error. Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "SomeServer". This is how I set up my Linked server: Provider: "Oracle Provider for OLE DB" Product Name: SomeServer Data Source: SomeServer Provider String: "Provider=OraOLEDB.Oracle;Data Source=SomeServer;User Id=MyLogin;Password=MyPassword" The query I run is: Select * from [Someserver].[schema or database]..[tbl_name] Any help??? What am i missing?
View Replies !
|
TRACKER
