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: Sunday, June 28, 2015 2:22 PM
Points: 1,396, Visits: 3,052
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: Monday, March 23, 2015 9:30 PM
Points: 82, Visits: 333
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: Monday, March 23, 2015 9:30 PM
Points: 82, Visits: 333
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: Sunday, June 28, 2015 2:22 PM
Points: 1,396, Visits: 3,052
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


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, July 22, 2015 2:19 AM
Points: 574, Visits: 1,231
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