SQL Server Equivalent of Oracle "Translate" command

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

David - 18 Mar 2008 18:50 GMT

I am in the process of moving some oracle sql into sql server 2005.

where decode(instr(translate(upper(job_id), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'~~~~~~~~~~~~~~~~~~~~~~~~~~'), '~'), 1, 1, 0) = 0

From what I'm told, the code above is used to check to see if the job_id
field has text in it. If it doesn't then its included in the select
statement. How would I do this in SQL Server?

Thanks in advance for your help!

Reply to this Message

Linchi Shea - 18 Mar 2008 19:41 GMT

I believe it checks whether the first character is a letter or a ~: (1)
upper() converts the entire string into upper case; (2) translate() in this
case replaces letters in the string with '~'; (3) instr(..., '~') returns the
position of the first ~; and (4) decode(?, 1,1,0) returns 1 if ? is 1 and
else returns 0.

Linchi

> I am in the process of moving some oracle sql into sql server 2005.
>
[quoted text clipped - 6 lines]
>
> Thanks in advance for your help!

Reply to this Message

David - 18 Mar 2008 19:47 GMT

Do you know how you would rewrite this in SQL Sever?

>I believe it checks whether the first character is a letter or a ~: (1)
> upper() converts the entire string into upper case; (2) translate() in
[quoted text clipped - 16 lines]
>>
>> Thanks in advance for your help!

Reply to this Message

Linchi Shea - 18 Mar 2008 20:14 GMT

It does seem to be a rather convoluted way of checking whether the first
character is a letter or '~'. For that, you can easily do it in T-SQL as
follows:

where ascii(@job_id) between 65 and 90 -- between A and Z
or ascii(@job_id) between 97 and 122 -- between a and z
or ascii(@job_id) = 126 -- ~

I'm not 100% on my Oracle string functions. So please double check.

Linchi

> Do you know how you would rewrite this in SQL Sever?
>
[quoted text clipped - 18 lines]
> >>
> >> Thanks in advance for your help!

Reply to this Message