asked on

masking decimals in t-sql

I'm using sql server 2008.

on my table i have a column called TestRate which is a numberic(10,4) dataype.

So the records in the column look like this:

10.2345
14.0345
16.4405

So in my select query, to query this column i'm doing something like this.

SELECT CONVERT(varchar,convert(numeric(10,4),MyTable1)) + ' rate per user'
FROM MyTable1

That query returns this:

10.2345 rate per user
14.0345 rate per user
16.4405 rate per user

How do i revise the query to mask the last 2 decimal digits with an X.

So i would want my result to look like this:

10.23XX rate per user
14.03XX rate per user
16.44XX rate per user

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pawan Kumar

Trial --

select LEFT ( CAST(a AS varchar(1000)) , LEN(CAST(a AS varchar(1000))) -2 ) + 'XX' from (
SELECT 10.2345 a UNION ALL
SELECT 14.0345 a UNION ALL
SELECT 16.4405 a )e

Open in new window

Output

/*------------------------

select LEFT ( CAST(a AS varchar(1000)) , LEN(CAST(a AS varchar(1000))) -2 ) + 'XX' from (
SELECT 10.2345 a UNION ALL
SELECT 14.0345 a UNION ALL
SELECT 16.4405 a )e
------------------------*/

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10.23XX
14.03XX
16.44XX

(3 row(s) affected)

Open in new window

Vitor Montalvão

Just out of curiosity. Why do you need to mask with XX? Rounding to 2 decimal places wouldn't be preferable for you?

maqskywalker

ASKER

Thanks. Its good to know how to mask the last for 2 digits of a 4 digit decimal number.
I ended up showing only 2 decimals.

Pawan Kumar

welcome !!