DECODE please help

Hi-

I am trying to accomplish this in my SELECT statement...

If the length of the retreived string data is more than 10 characters, it should return the first 10 characters followed by a literal string '..' else return the string data as is

I tried to use IIF, CASE but didn't got it work, kept getting errors...

SELECT FinalName = IIF ( DATALENGTH ( NameString ) > 10, SUBSTRING ( NameString, 0, 10 ) + '..' , NameString ) FROM SomeTable

Any help is highly appreciated... Thanks for your quick responses...T-SQL does not provide an IIF function. You need to use CASE.

SELECT
 CASE
 WHEN LEN(NameString) > 10 THEN SUBSTRING ( NameString, 0, 10 ) + '..'
 ELSE NameString
 END AS FinalName
FROM
 SomeTable

Terri|||And actually, for the SUBSTRING function you should be using a 1,10 not 0,10. And note that the LEN function would be more correct for your purposes than DATALENGTH.

Terri|||Thank you so very much Terri... This worked perfect...
:)

Decimal to ASCII conversion

I need to convert a decimal number to 2 ASCII characters using SQL. For example, 13110 (16 bit word which is 2 bytes) would be a decimal value representing 36 in ASCII.
Any ideas?I don't get the transformation fomula.

You get 36, I suppose the characters '3'+'6'. '3' has the ASCII value 51 (decimal) and '6' has the value 54 (decimal). I do not understand how 51 and 54 can be derivated from 13110. Can you help me?|||I'm sorry, actually it's quite straight forward: 51 is the one word, and 54 is the other word, and together they form your 13110 (decimal) or better to see 3134 (hex).

Anyway, the code is:
SELECT char(floor(13110/256)), char(13110-floor(13110/256)*256)

Cheers!|||That worked! You the Man! Thanks Bunches!