How to filter only numbers with 'Like' SQL

Asked

Viewed 64 times

-1

I have a table in which the field mainUnitId has the following data:

  • Ad23
  • Ad7
  • Ad11
  • Ad9
  • Adv1
  • Adv14
  • Adv21

I need to bring in the search only those who have Ad + numbers after

My code:

SELECT * FROM Lesson L WHERE L.mainUnitId like 'Ad__';

Expected return:

  • Ad23
  • Ad7
  • Ad11
  • Ad9

Thank you!

mysql sql select-sql where operator-like

  • only use "%" instead of "_": like 'Ad%'

  • Hello. In this case it will bring all values (Adv1,Adv14 and Adv21). I need the query to return only ( Ad23, Ad7, Ad11 and Ad9). Just the ones that start with "Ad" and have some number after.

  • 1

    then you’ll have to write a regular Expression for that

3 answers

2

You can use the following query:

SELECT * FROM lesson L WHERE L.mainUnitId like 'Ad[0-9]%';

Using a regular expression where after the characters Ad a numeric character (from 0 to 9) is found and the end the operator % accepting any character after this number.

0

The like method you can use the "%" operator as either a string suffix or prefix.

Example:

SELECT * FROM lesson L WHERE L.mainUnitId like 'Ad%';

OR

SELECT * FROM lesson L WHERE L.mainUnitId like '%23';

OR BOTH

SELECT * FROM lesson L WHERE L.mainUnitId like '%d%';

The underscore "_" which is what you’re trying to do only revolves around a character.

Example

SELECT * FROM ENTIDADES WHERE NOME_ENTIDADE like 'MAR_OS';

Upshot:

1 | Marcos

  • Hello. In this case it will bring all values (Adv1,Adv14 and Adv21). I need the query to return only ( Ad23, Ad7, Ad11 and Ad9). Only those that start with "Ad" and have some number later. If I use like 'Ad%' the values with Adv will be returned as well

  • 1

    Use the reverse, WHERE L.mainUnitId NOT LIKE 'Adv%'.

0

I was able to solve it this way:

SELECT * FROM Lesson L WHERE L.mainUnitId REGEXP 'Ad[0-9]';

Browser other questions tagged mysql sql select-sql where operator-like

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