Explode and compare value in a Select?

Asked

Viewed 117 times

0

In my Mysql I have the following column data_id it stores values in this format 120-01-01, what a SELECT make a EXPLODE in that field and checks whether the first value of the array which in case would be 120, matches the entered value?

Using only PHP would be easy to give a explode('-', $data_id) and then compare if the first key matches the desired value, but it would require me to search all the values of the table before, which is out of the question for obvious reasons, so I would like to know if it is possible to do the same within the SELECT.

Note: such a column is not a type field date, and yes a varchar

php mysql select

2 answers

0


I believe this is what you want:

SELECT SUBSTRING_INDEX($data_id, '-', 1)

  • Please collaborate with the quality of community content by better elaborating your response. Very short answers will hardly be clear enough to add anything to the discussion. If your response is based on some function or feature of the language/tool, link to the official documentation or reliable material that supports your response. Searching for similar questions in the community can also be interesting to indicate other approaches to the problem.

  • I understand is that in the case of select is simple even, but anyway, explaining, this select that I posted will return the first item before -, which is similar to php explode, to learn more about the operation of SUBSTRING_INDEX, when typing on google several trusted links will appear, one of them being: https://dev.mysql.com/doc/refman/5.7/en/string-functions.html

0

If it is always 3 characters, you can select the following

SELECT data_id, LEFT( data_id , 3 ) as codigo
  FROM sua_tabela
 WHERE LEFT( data_id, 3 ) = $variavel_que_esta_procurando

Now if they are different sizes, try using that function that was answered earlier 

SUBSTRING_INDEX(data_id, '-', 1)

Any questions just get in touch.

Hugs, Tonico

Browser other questions tagged php mysql select

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