Post reply

"FIND" within "SUBSTRING"?

  • November 15, 2009 at 1:41 pm

    #206580

    Facts:

    Routine task on Dynamic Tables

    Field contains XML data and Tags of nText type

    For example I need to routinelyINSERT '<FULLNAME>%</FULLNAME>' INTO tempTBL FROM tb_Lead *

    DELETE '<FULLNAME>%</FULLNAME>' FROM tb_Lead*

    Sample of field contents of varying length so cannot use set length amounts:

    '<Candidate><FullName>Neil Vicente</FullName><Email>email1@email.com</Email><Random1>jXzcfm^[[X</Random1><Random2>BGCYNxusZj</Random2><Random3>VHjMjnGZCi</Random3></Candidate>'

    '<Candidate><FullName>Jessie Hamaker</FullName><Email>email2@email.com</Email><Random1>oC`GCCVzFv</Random1><Random2>lTksRLBrgs</Random2><Random3>^SZt\VfR^T</Random3></Candidate>'

    '<Candidate><FullName>Tabatha Theiss</FullName><Email>email3@email.com</Email><Random1>cucMOqwDio</Random1><Random2>n]LKMaddu_</Random2><Random3>^T_sL_KuVZ</Random3></Candidate>'

    '<Candidate><FullName>Roslyn Rodenberger</FullName><Email>email4@email.com</Email><Random1>bNkiZPIzda</Random1><Random2>DHxutc\GwB</Random2><Random3>J]aRCIVS]J</Random3></Candidate>'

    '<Candidate><FullName>Darryl Palazzo</FullName><Email>email5@email.com</Email><Random1>l]fUj[vtBS</Random1><Random2>XEa\\_qcTL</Random2><Random3>EXdCXamuMG</Random3></Candidate>'

    '<Candidate><FullName>Hillary Lerman</FullName><Email>email6@email.com</Email><Random1>xa`oMUESJp</Random1><Random2>l_SXmfQQVR</Random2><Random3>UmYUf^qEjR</Random3></Candidate>'

    '<Candidate><FullName>Julio Bator</FullName><Email>email7@email.com</Email><Random1>GBi]I`LoCC</Random1><Random2>xUwUurVNRQ</Random2><Random3>N\zbeSn^rM</Random3></Candidate>'

    '<Candidate><FullName>Nelson Zobel</FullName><Email>email8@email.com</Email><Random1>ErfUzb`_ss</Random1><Random2>UO[az^eDLV</Random2><Random3>VVng\Zwp`H</Random3></Candidate>'

    '<Candidate><FullName>Cody Dobyns</FullName><Email>email9@email.com</Email><Random1>ivFokV^Qld</Random1><Random2>C]HLFOJKti</Random2><Random3>jtWmkHoKaL</Random3></Candidate>'

    What SQL command or WildCard am I missing that will extract this?

    I have come up with this solution that works great in Excel:

    =MID(A1,22,FIND("Email",A1)-34)

    However I have not found a comparable function to {"FIND"} in SQL server, unfortunately this does not work:

    SELECT SUBSTRING(Candidate, 22, FIND("Email",Extended)-34)) AS FullName FROM tb_Lead

    --------------------------------------------------------

    Msg 195, Level 15, State 10, Line 2

    'FIND' is not a recognized built-in function name.

    Although this below works fine in theory, it does not accurately capture the desired result since <FullName> is of variable length (larger or smaller than 12):

    SELECT SUBSTRING(Candidate, 22, 12) AS FullName FROM tb_Lead

    Would CHARINDEX and, or with PATINDEX be the solution, in what format?

  • November 15, 2009 at 1:50 pm

    #1079486

    You seem to of answered your own question, charindex would suit your needs.

    This link describes the uses of both Charindex and patindex

    http://www.databasejournal.com/features/mssql/article.php/3071531/Using-SQL-Servers-CHARINDEX-and-PATINDEX.htm

    BUT if you have XML why are you not using the internal XML processing ?


    Clear Sky SQL
    My Blog[/url]

  • November 15, 2009 at 2:00 pm

    #1079488

    I do not see how CHARINDEX can work for variable length strings.

    Although it is in fact XML data, I am only manipulating the text, not working with the XML in any other way.

  • November 15, 2009 at 2:20 pm

    #1079489

    I think that you will run into a myriad of performance issues further down the line

    But something like , ive broken it down to show the working but you should be able to get it back to one line , or a udf...

    Declare @SearchElement varchar(50)

    Declare @SearchElementEnd varchar(50)

    Declare @Start integer

    Select @SearchElement = "<FullName>"

    Select @SearchElementEnd = "</FullName>"

    Select @Start = charindex(@SearchElement,@XmlString)

    Select @End = charindex(@SearchElementEnd,@XmlString,@Start)

    Select substring(@XmlString,@Start+len(@SearchElement),@End-@Start)

    Im not in a position to test that right now, and this is not something that i would like in my production environment. Hope it helps you though


    Clear Sky SQL
    My Blog[/url]

  • November 15, 2009 at 2:48 pm

    #1079492

    I would use the code that David has given you, but I'd be careful because of performance issues.

  • November 15, 2009 at 5:51 pm

    #1079497

    Steve and David I really appreciate your insight, time, support and immediate responses!!

    FYI...This is purely a data manipulation tool in a c# application that will extract certain data, analyze, reorganize and merge it within a temp table, then insert back into new field within the running server, deleting the XML <nodes> as well as the temp table when finished.

    Know of anything existing that might also perform these tasks?

    I will keep you posted on the resulting progress.

    Enjoy your week!

    Regards,

    Mike

  • November 16, 2009 at 1:18 am

    #1079534

    thns the topic was very usefull

  • November 16, 2009 at 1:37 am

    #1079542

    Know of anything existing that might also perform these tasks?

    That'll be XML Processing

    Declare @test-2 xml

    Select @test-2 = '<Candidate><FullName>Neil Vicente</FullName><Email>email1@email.com</Email><Random1>jXzcfm^[[X</Random1><Random2>BGCYNxusZj</Random2><Random3>VHjMjnGZCi</Random3></Candidate>'

    select t.col.value ('FullName[1]', 'varchar(50)')as FullName,

    t.col.value ('Email[1]','varchar(255)') as Email

    from @test.nodes ('Candidate') AS t(col)


    Clear Sky SQL
    My Blog[/url]

  • November 16, 2009 at 1:48 am

    #1079545

    what dos the [1] mean on the select statment

  • November 16, 2009 at 2:11 am

    #1079556

    Its essentially the offset of the element required

    Declare @test-2 xml

    Select @test-2 ='<Elem><Child>One</Child><Child>Two</Child><Child>Three</Child></Elem>'

    select t.col.value ('Child[1]', 'varchar(50)')as Child1,

    t.col.value ('Child[2]','varchar(50)') as Child2,

    t.col.value ('Child[3]','varchar(50)') as Child3

    from @test.nodes ('Elem') AS t(col)


    Clear Sky SQL
    My Blog[/url]

  • November 16, 2009 at 2:17 am

    #1079558

    first thanx for help

    who can i make it run dynmicly (if i dont know the number of child how to make it retrive all of them)

  • November 16, 2009 at 2:31 am

    #1079566

    Then its subtly different

    Declare @test-2 xml

    Select @test-2 ='<Elem><Child>One</Child><Child>Two</Child><Child>Three</Child></Elem>'

    select t.col.value ('.', 'varchar(50)') as Child

    from @test.nodes ('Elem/Child') AS t(col)


    Clear Sky SQL
    My Blog[/url]

  • November 16, 2009 at 3:16 am

    #1079580

    first thanx alot for your great help

    can i ask about how is the shap of the xml variable parsing tree and how can i move through it and retrive date

Viewing 13 posts - 1 through 12 (of 12 total)

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