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: Tuesday, January 21, 2014 8:55 AM
Points: 1,520, Visits: 3,035
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: 2 days ago @ 10:40 PM
Points: 82, Visits: 316
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: 2 days ago @ 10:40 PM
Points: 82, Visits: 316
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: Tuesday, January 21, 2014 8:55 AM
Points: 1,520, Visits: 3,035
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 07, 2013 12:09 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 10, 2014 6:20 PM
Points: 220, Visits: 695
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