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