Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Convert Varchar to Decimal Expand / Collapse
Author
Message
Posted Thursday, February 14, 2013 8:43 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 27, 2014 6:26 AM
Points: 1,521, Visits: 3,036
Went looking on the net and found an answer right here in SSC (of course!!). If you want to test for all digits, the expression NOT LIKE '%[^0-9]%' should do it. Do read the entire post from Jeff Moden about testing for numbers.
Post #1420367
Posted Thursday, February 14, 2013 9:50 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, July 25, 2014 6:03 AM
Points: 82, Visits: 317
PATINDEX('%[^0-9]%', LHP_PARAM_RESULT_VALUE) = 0

I am just mentioned a name only.
Post #1420372
Posted Thursday, February 14, 2013 9:53 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, July 25, 2014 6:03 AM
Points: 82, Visits: 317
PATINDEX('%[^0-9]%', LHP_PARAM_RESULT_VALUE) = 0
Post #1420374
Posted Thursday, February 14, 2013 10:15 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 27, 2014 6:26 AM
Points: 1,521, Visits: 3,036
raguyazhin (2/14/2013)
PATINDEX('%[^0-9]%', LHP_PARAM_RESULT_VALUE) = 0

The key, then, is the Not-to-match operator, "^". Zero returned means PATINDEX() found no characters other than digits, 0-9. Got it! Thanks for the clarification.
Post #1420379
Posted Tuesday, May 7, 2013 12:09 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, July 28, 2014 11:45 AM
Points: 317, Visits: 823
In your query you are casting as decimal(18,2). Are your numbers always going to be whole numbers?

The following example
select PATINDEX('%[^0-9]%', '1234234.20') 

does not return zero.
Post #1450286
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse