How to check format of string number variable will format to Decimal(32,6)

Question

How to check format of string number variable will format to Decimal(32,6)

Weegee2017

SSC Eights!

Points: 994
More actions
October 6, 2020 at 7:21 pm

#3793360

I have an incoming set of string values which need to be checked so they can be formatted as Decimal(32,6) in a table
These can range for example:
100000
23.6767
4567.12121212
Whats the best way to check these incoming values ?
is it to using string manipulation using the decimal point to count digits?
I tried Try_Convert thinking that it would return a Boolean value but didn't work
declare @ReserveAuthorisedTotal1 varchar(100) = '2344444444.48888888'
select TRY_CONVERT(decimal(32,6),@ReserveAuthorisedTotal1)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Mike01 SSChampion Points: 11393 More actions · Answer 1

Your example works because it can convert it to Decimal (32,6). It rounded the last value. The try convert will return null if it can't convert it. See below

declare @ReserveAuthorisedTotal1 varchar(100) = '2344444444.48888888X'

select TRY_CONVERT(decimal(32,6),@ReserveAuthorisedTotal1)

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeffrey Williams SSC Guru Points: 90249 More actions · Answer 2

If you need to identify when a value can be converted successfully but is rounded or truncated then you need to check the value returned from try_cast/try_convert against the original value:

Declare @testTable Table (ReservedAuthorizedTotal varchar(100));
 Insert Into @testTable (ReservedAuthorizedTotal)
 Values ('2344444444.48888888')
      , ('11111111111111111111111111.333333')
      , ('222222222222222222222222222.4444444')
      , ('22222222222222222222222222.444444');

 Select *
      , try_cast(tt.ReservedAuthorizedTotal As decimal(32,6))
      , iif(cast(try_cast(tt.ReservedAuthorizedTotal As decimal(32,6)) As varchar(100)) = tt.ReservedAuthorizedTotal, 'True', 'False')
   From @testTable tt;

You will either get a NULL returned because it cannot be converted or you will get a different value (rounded or truncated). From this you can then determine how you want to handle those 'bad' values.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Weegee2017 SSC Eights! Points: 994 More actions · Answer 3

I wasn't aware of Try_Cast so will check that out

Thanks guys.That really helped