How to verify the occurrence of a string in a row and pick up x characters on the left and y on the right?

Asked

Viewed 968 times

3

I’m having a problem with a Mysql query and I’m looking for help. Suppose I have many lines in the PROCESSES column and one of them is like this:

IBAMAPROCUR: DF00025372 ADRIANA MAIA VENTURINIAPDO: JOSE DOS

I want to check if there are 25372 caractares on that line, if there is, I want to get 10 characters before the spot and 10 characters after. And check if in this range there is the acronym DF. If it exists, it shows the line. This way I would be sure that it is a number of OAB Lawyer. Someone would give me a help to assemble a select for that reason?

mysql sql database select

  • How is the content of the column PROCESSOS? The way you’re describing the problem, and considering how the @Pasch solution doesn’t suit you, I think you need to first normalize your data, in a stride offline, for a relational model; once this is done, the solution to your problem will probably be quite simple.

  • Eh, that’s what I did... I thought there was a way to do it directly in the query because it would be, I think, faster due to the selected search algorithms that SQL integrates. Thank you

1 answer

1

Hello.

A common select with Where doesn’t suit you?

SELECT Name, Processes FROM (Table) WHERE Processes LIKE '%df%' AND Processes LIKE '%25372%'

  • No friend, because that would take data from the whole line and it can be beeem great. Hence it would be false true. This line can have many numbers, many acronyms... hence it risks giving true even if it is not what I seek. It has to be in an interval: xxxxxx25372xxxx If any of these X are DF then tah right

  • Is the number always this (25372)? And the number of characters up to the DF is always this? If so, you can use underline. Type: SELECT Name, Processes FROM Table WHERE Processes LIKE '_____________DF__25372%'. Underlines are substitutes for any character that comes in its place. That way it should work.

  • My old man, blaza? The problem is that there are thousands of oabs... and the acronyms are from all states. The line can have the contents of several converted PDF pages or 100 characters. The OAB and acronym do not follow a position pattern in this Slide. Month whenever the number is found and there is the acronym next to the number then the chance of being the OAB q to looking infinitely increases. I cannot mount the command. The program eh java if, and the data for the query are passed by parameter. So I need, if on this line you have the number and near it you have the acronym return truth. Got it? Oblige

  • I am currently doing a like with number, dai with the answer I filter by java, but are millions of lines and. thousands of oabs, dai to trying to improve performance in processing and this query I think q would be my solution.

  • @Hadsonmarcelogomes Cara, then it gets really complicated. What I suggested would work well if the number always appeared in the same place. But is the number always this 25372? I’ll see if I can find some other way to look for it on any piece of a column. And another question: it is better to first look for the line number and, if it exists, then check if there is the acronym. Correct?

  • Blza? So, there are hundreds of thousands of numbers... Yes it is better to look for the number first but two select would not affect the performance? Vlw

  • @Hadsonmarcelogomes Well, you should be able to do it with just one select. If you have any news, please let us know.

Show 2 more comments

Browser other questions tagged mysql sql database select

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