Conversion of query from Oracle to SQL Server

Hi Friends,
Need ur help desperately. I am stuck with one of the queries which i had written in Oracle and need the same in SQL Server.Please have a look at the following query :

select * from r_tin_1099_info where instr(translate( nm_ctrl_cd , '~!@.#$%^&*()_+}{":?><`-=]['''';/., ', '*******************************' ),'*') > 0;

Basically my purpose is to replace the values in column NM_CTRL_CD having wild card characters with '*' and then select this rows to display.

However i am not able to run the same query in SQL Server since TRANSLATE is not a built in func. I have tried a lot to replace it but could only one func : REPLACE . But the same will not replace any one of the above wild characters but will replace the entire pattern.Please note that it should be able replace even if one of the wild card characters are present in the string and not necessarily the entire pattern shown above.

please reply ASAP since i am working and need this query to fix a defect.

Thanks in advance.Hi all,
Can somebody please reply to my query mentioned above !!!|||I'd suggest using:SELECT *
FROM r_tin_1099_info
WHERE nm_ctrl_cd LIKE '%[]~!@.#$%^&*()_+}{":?><`-=[]%'
-PatP|||Hey thanks a ton... I will try this out and let you know about the results !!!|||Hi,
The query u have sent does not return any result even though the data is there in the table.Can you please help us out with the query !!

Thanks|||Sorry, I was trying to avoid using escape characters and that got me into trouble. A better solution is:SELECT *
FROM r_tin_1099_info
WHERE nm_ctrl_cd LIKE '%[~!@.#$%^&*()_+}{":?><`x-=x]['';/., ]%' ESCAPE 'x'-PatP|||hey Pat... thanks a lot.. this is working fine... just another question... do u knw any equivalent func for TRANSLATE(in DB2)... bcoz i have a query in DB2 which needs to be translated in SQL Server and since TRANSLATE is not a built in func.. i am not able to execute the same query. the query is as follows:

update r_tin_1099_info set nm_ctrl_cd = substr(replace(translate( coalesce(last_nm,tin_nm_1), '', '~!@.#$%^&*()_+}{":?><`-=]['''';/., ' ),' ',''),1,4) where locate('*',translate( nm_ctrl_cd , '*******************************', '~!@.#$%^&*()_+}{":?><`-=]['''';/., ' )) > 0

I know i mite be asking to much from you.. but it will gr88 if u can guide me with this query as well !!

Thanks.|||Because the second argument to the Translate() call is an empty string, the Translate function will do nothing, it is meaningless so it can be discarded. That leaves you with:UPDATE r_tin_1099_info
SET nm_ctrl_cd = substr(replace(coalesce(last_nm, tin_nm_1), ' ', ''), 1, 4)
WHERE nm_ctrl_cd LIKE '%[~!@.#$%^&*()_+}{":?><`x-=x]['';/., ]%' ESCAPE 'x'-PatP|||hey pat... i tried ur query... its executing without any errors but doesnt seem to update the records... the values having wild characters are not updated and still contain the wild characters... Can you please help me out with this

thanks.|||If you can tell me what you want, I can probably help. The code that you posted in your last question ought to have exactly the same effect as the code that I posted in response to it, but that doesn't appear to be what you actually want.

As I've given you several examples to work from, you ought to be able to get pretty close to what you want on your own. If not, please post:

1) whatever DML you have working
2) A DDL script to build your schema
3) At least a few sample rows of data (BCP native format would be preferred)
4) An example of the output that you'd like from your query

I'll help you, but I can't read your mind and I won't actually do your job for you.

-PatP

Controlling the language returned from Cubes

Hi!
Hopefully someone can help me with this, because I am really stuck.
I am reporting from a cube whose members have captions defined for
multiple languages. I would like to control the language displayed using a
report parameter. These reports will be rendered using Web Services.
Is there a way I can set the language setting of the query dynamically?
Setting the locale for the report did not seem to make any difference :>(.
Any help would be greatly appreciated.
Thanks,
BobBob Hug wrote:
> Hi!
> Hopefully someone can help me with this, because I am really
> stuck. I am reporting from a cube whose members have captions
> defined for multiple languages. I would like to control the language
> displayed using a report parameter. These reports will be rendered
> using Web Services. Is there a way I can set the language setting
> of the query dynamically? Setting the locale for the report did not
> seem to make any difference :>(.
> Any help would be greatly appreciated.
> Thanks,
> Bob
Could the rs:ParameterLanguage be a help for you?
http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm
roland|||Can't you just ask for the appropriate member caption in whatever language
you want?
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/agmemberprops_8ier.asp.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bob Hug" <rhug@.brassring.com> wrote in message
news:uoIhIAVXEHA.3988@.tk2msftngp13.phx.gbl...
> Hi!
> Hopefully someone can help me with this, because I am really stuck.
> I am reporting from a cube whose members have captions defined for
> multiple languages. I would like to control the language displayed using a
> report parameter. These reports will be rendered using Web Services.
> Is there a way I can set the language setting of the query dynamically?
> Setting the locale for the report did not seem to make any difference :>(.
> Any help would be greatly appreciated.
> Thanks,
> Bob
>|||Brian,
Thanks for the response.
The cube is configured exactly as your reference
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/agmemberprops_8ier.asp
describes. My problem is how to control the language that is returned in the
report. I believe that the cube is correctly configured. Analysis Manager
and OWC both display the expected language based upon the language setting
of the client. Reporting Services' reports return the expected language when
I set the locale of the Reporting Server. The locale of the Reporting Server
seems to determine the language that the cube returns, perhaps because this
is determining the language of the connection? When I change the Report
Server's locale to French, for example, I get French returned like I expect.
Perhaps this simply a reflection of my lack of MDX knowledge, but I have not
been able to find a way to specify in the query the language I want in
effect. There does not seem to be anything like 'Set Language'.
Any ideas?
Thanks,
Bob
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
news:e1uhcOaXEHA.1888@.TK2MSFTNGP11.phx.gbl...
> Can't you just ask for the appropriate member caption in whatever language
> you want?
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/agmemberprops_8ier.asp.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Bob Hug" <rhug@.brassring.com> wrote in message
> news:uoIhIAVXEHA.3988@.tk2msftngp13.phx.gbl...
> > Hi!
> > Hopefully someone can help me with this, because I am really stuck.
> > I am reporting from a cube whose members have captions defined for
> > multiple languages. I would like to control the language displayed using
a
> > report parameter. These reports will be rendered using Web Services.
> > Is there a way I can set the language setting of the query
dynamically?
> > Setting the locale for the report did not seem to make any difference
:>(.
> >
> > Any help would be greatly appreciated.
> >
> > Thanks,
> > Bob
> >
> >
>