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


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,388, Visits: 3,039
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: Today @ 6:14 AM
Points: 82, Visits: 324
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: Today @ 6:14 AM
Points: 82, Visits: 324
PATINDEX('%[^0-9]%', LHP_PARAM_RESULT_VALUE) = 0
Post #1420374
Posted Thursday, February 14, 2013 10:15 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,388, Visits: 3,039
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: Wednesday, September 24, 2014 4:50 PM
Points: 358, Visits: 876
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