Post reply

How to Extract some part of text from varchar.

  • 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

  • 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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply