Filter SQL query

Asked

Viewed 382 times

1

I own a field called emailMSG0 within a table in the database. This field contains various information. Is there any possibility in SQL I filter what I want to get inside this field emailMSG0 ? Bring only some information.

If I do a normal select for example:

SELECT emailMSG0 FROM TABELA

Give me that back:

Prezado Cliente <b><br><br>Pedido: <b>4264136</b>, NF: <b>1448692</b> <br>Pedido: <b>4264138</b>, NF: <b>1448693</b> <br>Pedido: <b>4264140</b>, NF: <b>1448694</b> <br><br>Data: <b>2015-12-08 10:37:49</b> Hora: <b>2015-12-08 10:37:49</b><br><br>Link do comprovante: <br><br><img src='cid:1krrpu5mb4br3'/>

Is there any way to filter out what I want to appear ? Like I just want to get back the requests from this field.

database sql-server select

2 answers

3

If you want to obtain only part of the contents of a field that stores XML, the ideal is to use an application external to SQL to handle this information. You can do with SQL, but it is not the most suitable tool for various reasons:

  • SQL Server is poor in API’s to work with XML;
  • Even if it wasn’t, the Transact-SQL language is procedural. Any code to handle XML is probably much longer and more complex than if you used an object-oriented language, for example;
  • If you are storing XML with a well-defined schema in the database, maybe you should rethink your application to transform XML data into another more normalized format to store in the database.

If you still want to move on, you can use Xquery. It is supported from SQL Server 2008. The link there is the official documentation, translated by machine to PT. I suggest taking a look at the documentation in English if you are fluent.

  • It’s not really XML, I just wanted to take something specific of this field so I don’t have to mess with the structure of the database because I will use this information in a web application.

  • I don’t know your context, but even without looking I would bet a PS4 that going down this path will only make your work more difficult in the medium and long term.

  • 1

    That’s not xml, that’s html.

  • I do not touch the bank only in the web application but to make the query I need to filter it there, and the easiest I believe I would treat in sql that makes the query.

  • I think you had a question similar to that yesterday http://answall.com/questions/129211/occult-informs%C3%A7%C3%B5es-do-bank-no-php And I believe that to extract certain snippets from the string you will have to deal with this in PHP, finding a logic to implement in some way

  • And from what I understood, you want to take information of order number and invoice number, from a field where you keep the email message with all this data and some other information

  • I tried to find in PHP but in SQL query I think it would be more effective, the work in php doubles to do this.

  • Face I believe that treating this in PHP would be less expensive for you, since in the query you would have to use a lot of locates and substrings to do this and would not have a stable result, I believe that in the application would be easier to treat this.

  • You have substring_index too, but you don’t have a fixed number of orders in that field, do you? And even then it would still be costly.

  • It is the numbers are not fixed, the right would be to create a column with requests one with the NF but I can not move the structure, I will have to find something in the application same. Is there any solution ?

  • Expensive one of the possible solutions would be you take the string that returned from the bank and give a explode('Orders:', $return); from the index 1 of the array you will already have only the order numbers and nfs, but this is only the first step ai you will have to better deal with it, but it is already a way

  • I answered there, is more or less what has to be done with the return of the database

Show 7 more comments

3


A roughly speaking in PHP would be more or less what you would do as the return of the database

  <?php
        $retorno="Prezado Cliente <b></b><br><br>Pedido:<b>4544</b>NF:<b>4441</b>Pedido:<b>4543</b> NF:<b>4442</b>Pedido:<b>4546</b>NF:b>4443</b>Pedido:<b>4545</b>NF:<b>4444</b>Pedido:<b>4547</b>NF:<b>4445</b>";

        $teste = explode('Pedido:',strip_tags(explode('Data:', $retorno)[0], "<b></b><br>"));
        for($i =1; $i < sizeof($teste); $i++){
            $ped_nf = explode("NF:", $teste[$i]);
            echo ' Pedido: '. $ped_nf[0].' NF: '.$ped_nf[1];
        }
        //print_r($teste);
?>

It may take some adjustments, but this is one of the ways.

  • Two explodes in the string to separate the order part and already removing the HTML tags.
  • Already starting to scroll through the array at index 1, as position 0 was only the name of the Client.
  • Then another explodes to separate NF requests and it’s life that follows. I hope it helps.

  • Man, very good adapted worked, but after the requests have more information as I put an end to it ? Set a position to stop scrolling through the array. Soon after you have the date information, stop when you arrive at the date there is this possibility ?

  • Put an example of String ai to take a look, but probably this rest will be the last position of the ai array is just don’t take it, if that’s it in the proper is with sizeof() -1 already solves.

  • Okay, I’ll edit my question with her there.

  • It’s set there not to pick up after the date

  • Perhaps your answer would fit better in this question that I asked earlier to is.http://answall.com/questions/129211/concealing-informs%C3%A7%C3%B5es-do-banco-no-php? noredirect=1#comment269239_129211

  • Beauty I’ll put there too there I don’t know how moderation will treat this; Good! they know what to do. That’s right, I’m glad you solved your problem.

Show 1 more comment

Browser other questions tagged database sql-server select

You are not signed in. Login or sign up in order to post.