Split string in sql server 2000

  • Sandeep Mahajan-393729

    SSC Eights!

    Points: 858

    How to split string in sql server 2000

    suppose i have a string  Like this  ----

    <TD>

    <P align=justify><STRONG>Legal Perspective</STRONG> </P>

    <P align=justify><IMG height=206

    src="http://www.fsoutsourcing.com/images/bestCaptive.jpg" width=387></P>

    <P align=justify><B>By: </B><SPAN

    style="FONT-WEIGHT: 700; FONT-SIZE: 12pt; FONT-FAMILY: Times New Roman">Paul

    J.N. Roy and Percival Billimoria Esq</SPAN></P>

    <P><STRONG>With its near-shore location and a strong domestic IT market, Brazil

    is actively promoting itself as an IT services offshore

    destination.</STRONG></P>

    <P>Many companies are increasingly interested in setting up offshore operations.

    While the cost savings and other rewards in doing so may be significant, there'   

    i want to remove <img....> tag from this string..

    Plz help...... i m in a great trouble...

    Plz..

  • MarkusB

    SSC-Dedicated

    Points: 37278

    Since your input is a xml document check out sp_xml_preparedocument.

    BOL has some examples.

    Markus

    [font="Verdana"]Markus Bohse[/font]

  • Sandeep Mahajan-393729

    SSC Eights!

    Points: 858

    i don't understand. Plz give me example....

    Plz..

  • David Burrows

    SSC Guru

    Points: 64394

    quote Since your input is a xml document check out sp_xml_preparedocument

    The HTML given is not XML compliant

    One way to get the first img is

    STUFF(LEFT([value],CHARINDEX('>',[value],CHARINDEX('<img',[value]))),1,CHARINDEX('<img',[value])-1,'')

    Far away is close at hand in the images of elsewhere.
    Anon.

  • David Burrows

    SSC Guru

    Points: 64394

    Just thought

    Do you want to extract or remove the img tag

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Sandeep Mahajan-393729

    SSC Eights!

    Points: 858

    yes, i want to remove <img..> tage from whole string.....

    Plz explain in detail... I m New for SQL

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714078

  • mrpolecat

    SSCertifiable

    Points: 6934

     

    substitute your field for @string.  This will remove the first instance assuming the html conforms to <IMG..yada..> with no '>' in the image tag.

     

    select substring(@string,1,charindex('<IMG',@string)-1) +

    substring(@string,charindex('>',@string,charindex('<IMG',@string))+1,len(@string)-charindex('>',@string,charindex('<IMG',@string))+1)


  • Sandeep Mahajan-393729

    SSC Eights!

    Points: 858

    Thank you sir.

    thank you very much... My problem is solved now.

     

  • Spartan89

    Newbie

    Points: 1

    Hello use this.

    I have to made an SP to split a string character.

    I hope that be useful,

    Greetings.

    CREATE PROCEDURE SP_STRING_SPLIT (@String varchar(8000),@Separator Char(10),@pos_select int=0)

    AS

    BEGIN

    SET NOCOUNT ON

    DECLARE @Caracter varchar(8000)

    DECLARE @Pos int

    Set @Pos=1

    Set @Caracter=''

    CREATE TABLE #ARRAY

    ( String varchar(8000) NOT NULL,

    Pos int NOT NULL IDENTITY (1, 1)

    )

    While (@Pos<=len(@String))

    Begin

    If substring(@String,@Pos,1)=Ltrim(Rtrim(@Separator))

    Begin

    INSERT INTO #ARRAY SELECT @Caracter

    SET @Caracter=''

    End

    Else

    Begin

    --give shape the wordo

    Set @Caracter=@Caracter+substring(@String,@Pos,1)

    End

    If @Pos=len(@String)

    Begin

    INSERT INTO #ARRAY SELECT @Caracter

    End

    SET @Pos=@Pos+1

    End

    SELECT Pos,String FROM #ARRAY where (Pos=@pos_select Or @pos_select=0)

    END

    GO

    exec SP_STRING_SPLIT 'HELLO, HOW ARE YOU?',',',0

     

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

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