How to Extract some part of text from varchar.

• asita

  SSCarpal Tunnel

  Points: 4144

  More actions

  July 28, 2017 at 12:35 pm

  #339135

  Hello Good Morning,

  Can you please let me know this... need to extract number from varchar string in below case

  Create Table #MyTask (Detail1 Varchar(100))

  Insert Into #MyTask Values ('1.5% or below')
  Insert Into #MyTask Values ('<= 1.7% May be but will check')
  Insert Into #MyTask Values ('will be need to match 2% May be but will check')

  Select * from #MyTask

  How to extract 0.015 from row 1, 0.017 from row 2 , .02 from row 3 ? please advise

  Thank you in Advance
  Asita

• asita

  SSCarpal Tunnel

  Points: 4144

  More actions

  July 28, 2017 at 12:51 pm

  #1953242

  I tried this

  SELECT * ,

  SUBSTRING(Detail1, PATINDEX('%[0-9]%', Detail1), CHARINDEX ('%', SUBSTRING(Detail1, PATINDEX('%[0-9]%', Detail1), LEN(Detail1)))) Extraction_of_Number ,

  CAST ( SUBSTRING(Detail1, PATINDEX('%[0-9]%', Detail1), CHARINDEX ('%', SUBSTRING(Detail1, PATINDEX('%[0-9]%', Detail1), LEN(Detail1)))-1) as decimal(3,2))/ 100 Percentage_Value

  FROM #MYTASK