Post reply

Select a part of text from the xml

  • February 18, 2014 at 5:24 pm

    #288246

    I have an xml stored in a coulum of a table and I use the following query to extract an xml element :

    select CONVERT(XML,CONVERT(NVARCHAR(max),Response)).value('(/Quote/error)[1]','nvarchar(max)') as Excepiton .

    The result of the expression is :

    TL43:The product has no marked price.;

    I would like to select only the code : TL43

    and then seperately I would like to select The product has no marked price.

    Is there a way I can do it ?`

  • February 18, 2014 at 5:30 pm

    #1690108

    You could use CHARINDEX and SUBSTRING functions like this:

    WITH Samp AS(

    SELECT 'TL43:The product has no marked price.;' AS SomeText

    )

    SELECT LEFT( SomeText, CHARINDEX(':', SomeText) - 1),

    SUBSTRING(SomeText, CHARINDEX(':', SomeText) + 1, CHARINDEX(';', SomeText)-CHARINDEX(':', SomeText)-1 /*Or change this to a large number to include all the rest of the string*/)

    FROM Samp

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

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

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