Post reply

Extract certain string before a particular string

  • December 15, 2021 at 11:48 pm

    #3964186

    Hello!

    I have column of datatype 'text' with data like below:

    <ID=“123” total=“20.00” number=“01-093450”  name=‘test, test1” />

     

    How do I only get the value 01-093450 from the string value above?

     

    Thanks in advance.

  • December 16, 2021 at 12:31 am

    #3964192

    IF you want to extract the value after 'number="' in the string:

    SELECT *,
        SUBSTRING(string, NULLIF(CHARINDEX('number="', string), 0) + 8, 
            CHARINDEX('"', SUBSTRING(string, CHARINDEX('number="', string) + 8, 100)) - 1) 
            AS number_value
    FROM ( VALUES('<ID=“123” total=“20.00” number="01-093450"  name=‘test, test1” />') ) AS data(string)

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

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

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