Convert the Numeric data type to varchar without trailing Zeros in sybase

Question

How to Convert the SQL data type Numerci(15,2) to string(varchar data type) without adding the trailing zeros in sybase.

Example- in column abc below values are present-

0.025
0.02
NULL
0.025589
5.289

on running the query-

select STR(column,10,4) from table --- produces the results 0.025,0.0200
select CAST(column as CHAR(5)) from table -- produces the results as 0.0250 etc

I can not do it in presentation layer Can someone please help with query.

ANSI SQL: `trim(trailing '0' from cast(column as varchar(20)))` — jarlh, Jan 04 '18 at 13:34
If the above comment by @jarlh does not work, then I think you would need some kind of regex support to do this. Or, you could handle it in your presentation layer. — Tim Biegeleisen, Jan 04 '18 at 13:37
using regex is sometimes an overkill on performance of sql query — SSD, Jan 04 '18 at 13:43
@SurjitSD How else do you plan to strip an arbitrary number of zeroes from the end of a decimal number? I don't think Sybase's base string functions can handle this. — Tim Biegeleisen, Jan 04 '18 at 13:43
I wouldn't worry that much about a regex in the select list. (When in WHERE is another story.) — jarlh, Jan 04 '18 at 13:48

markp-fuso · Answer 1 · 2018-01-04T15:59:43.860

Unfortunately Sybase ASE does not have any native support for regex's, nor any out-of-the-box functions for stripping trailing zeros.

An obvious (?) first attempt might consist of a looping construct to strip off the trailing zeros, though it'd probably be easier to reverse() the initial string, strip off leading zeros, then reverse() to get back to the original value. Unfortunately this is not exactly efficient and would need to be encapsulated in a user-defined function (which introduces an additional performance hit each time it's invoked) in order to use it in a query.

The next idea would be to convert the zeros into something that can be (relatively) easily stripped off the end of the string, and it just so happens that ASE does provide the rtrim() function for stripping trailing spaces. This idea would look something like:

convert all zeros to spaces [str_replace('string','0',' ')]
strip off trailing spaces [rtrim('string')]
convert any remaining spaces back to zeros [str_replace('string',' ','0')]

** This obviously assumes the original string does not contain any spaces.

Here's an example:

declare @mystring varchar(100)

select  @mystring = '0.025000'

-- here's a breakdown of each step in the process ...

select ':'+                               @mystring                    + ':' union all
select ':'+                   str_replace(@mystring,'0',' ')           + ':' union all
select ':'+             rtrim(str_replace(@mystring,'0',' '))          + ':' union all
select ':'+ str_replace(rtrim(str_replace(@mystring,'0',' ')),' ','0') + ':'

-- and the final solution sans the visual end markers (:)

select str_replace(rtrim(str_replace(@mystring,'0',' ')),' ','0')
go

 ----------
 :0.025000:
 : . 25   :
 : . 25:
 :0.025:

 --------
 0.025

If you need to use this code snippet quite often then you may want to consider wrapping it in a user-defined function, though keep in mind there will be a slight performance hit each time the function is called.

score -1 · Answer 2 · answered Jan 05 '18 at 06:30

Following approaches can be used - 1) It uses the Replace function

select COLUMN,str_replace(rtrim(str_replace(
       str_replace(rtrim(str_replace(cast(COLUMN as varchar(15)), '0', ' ')), ' ', '0')
       , '.', ' ')), ' ', '.')
from TABLE

Output -

0.025
2

2) Using Regex-

select COLUMN ,str(COLUMN ,10,3),  
    reverse(substring( reverse(str(COLUMN ,10,3)), patindex('%[^0]%',reverse(str(COLUMN ,10,3))), 10)) 
from TABLE

Output -

0.025
2

0.025
2.

Convert the Numeric data type to varchar without trailing Zeros in sybase

2 Answers2

Output -

Output -