June 4, 2002 at 3:10 pm
Greetings.
This is not the first place I have asked this question, but I am not quite there yet, so I need to ask here also.
I am porting an Oracle query to ms sql server 7/2000.
The query selects rows that match a value if the column has only alpha-numerics (i.e. punctuation, etc. is removed).
In Oracle this can be achieved using a translate function.
Any thoughts on doing in for ms sql 7.0?
I tried the replace, but I did not know how to replace single quotes since they are delimeters in the replace function (and it looked a bit complex/big).
If I have a table with a single column containing:
'ABC123'
'ABC!@#$%^&&*()123'
'AZ'
Then the select on 'ABC123' would return the rows:
'ABC123'
'ABC!@#$%^&&*()123'
Thanks for any assistance,
Jeff
June 4, 2002 at 5:54 pm
Other than replace I know of nothing. As for ' use '' to replace, REPLACE(col,'''','') will do the trick. Also just so I fully understand can you explain how translate works. I don't have a good reference here on it. Maybe there is something I haven't thought of.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 5, 2002 at 6:49 am
OK, thanks for the 'replace' tutorial.
Translate .. well here is an Oracle blurb, but first my overview.. argument is three strings. First string is data of interest.
Second is a list of characters and third is a list of characters to translate or replace those in the second parameter with. So
select translate('hey - you',' -','$') from table would give you 'hey$$you' for each row in the table.. the dash got yanked (no replacement in its position in the third string) and the space got replaced by the dollar sign.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply