How to use LIKE in a SQL statement to look for a number followed by the "_" character within the contents of a field?

Question

Ex: how to look for 19_ or 21_ ?

I know that _ is a reserved wildcard.... but I would need to use it here as an actual character... THANKS!!

I have a classes table. Each class has a users text field. The text field looks like this (for a record):

19_m~43~2~4-m~45~0~0-m~46~0~0-m~47~0~0-m~48~0~0-m~49~0~0-m~50~0~0-m~51~0~0-t~6~0~0-t~7~0~0-t~8~0~0-t~9~0~0-m~52~0~0-m~53~0~0-m~54~0~0-t~10~0~0-t~11~0~0-t~12~0~0-t~13~0~0|21_m~43~0~0-m~45~0~0-m~46~0~0-m~47~0~0-m~48~0~0-m~49~0~0-m~50~0~0-m~51~0~0-t~6~0~0-t~7~0~0-t~8~0~0-t~9~0~0-m~52~0~0-m~53~0~0-m~54~0~0-t~10~0~0-t~11~0~0-t~12~0~0-t~13~0~0

The user ids are the numbers that have _ after them. In this example, 19_ and 21_.

What I am trying to do is to

select * from the classes table where a certain user (number followed by _)

is present in this USERS field.

I tried something like [...] prepare

("SELECT * from classes WHERE USERI LIKE '".$id."\\_%'")

but would not get me this record... it returns an empty result.

Tag your question with the database you are using. It sounds like you want regular expressions, not `LIKE`. — Gordon Linoff, Feb 19 '21 at 18:19
Does this answer your question? [MySQL LIKE query with underscore](https://stackoverflow.com/questions/22167132/mysql-like-query-with-underscore) — β.εηοιτ.βε, Feb 19 '21 at 18:21
Can you post some sample data and the desired output? Hard to tell what exactly you're looking for — Radagast, Feb 19 '21 at 19:21

score 0 · Answer 1 · edited Feb 20 '21 at 02:32

0

Try to use backslash:

SELECT * from classes WHERE USERI LIKE '19\_'

edited Feb 20 '21 at 02:32

Raksha Saini

604
12
28

answered Feb 19 '21 at 18:25

This should look like the answer, but it still doesn't work.. I will edit the question now for more details :) THANKS – dutu000 Feb 19 '21 at 19:37
Which database do you use? I'm trying `LIKE '19_%'` in MySQL - it works. – Feb 19 '21 at 22:01
i use mysql from a server.. don't know the version.. I'll retry – dutu000 Feb 19 '21 at 22:26
You're blocked my account. I must collect positive results from my answers but I can't answer to anything. Thanks! – Feb 24 '21 at 08:21

score 0 · Answer 2 · answered Feb 19 '21 at 18:38

0

If you do not know in advance what could be the number you are looking for before the underscore, just use REGEXP.

Example: SELECT * FROM my_table WHERE field_whith_number REGEXP '^([0-9])+_';

Brief explanation of what is happening in REGEXP:

^ match the start of the line

([0-9])+ match 1 or more number

_ match a single underscore

Please note that this is not an ideal solution with very big tables since it will do a table scan.

answered Feb 19 '21 at 18:38

herkil

146
3

Thanks for the answer. I have my number stored in the $id variable. – dutu000 Feb 19 '21 at 19:49

score 0 · Answer 3 · answered Feb 19 '21 at 20:15

0

If you're using SQL Server for the queries, it could be accomplished with something like

...
WHERE USERS LIKE TheID + '#_' ESCAPE '#'

The ESCAPE after LIKE designates an escape character which can be used to specify special characters as literals.

answered Feb 19 '21 at 20:15

Andy

3,132
4
36
68

thanks!! I'll give it a try. I've seen this "escape" thing but didn't quite get how to use it. Now you made it clear for me :) – dutu000 Feb 19 '21 at 22:23

How to use LIKE in a SQL statement to look for a number followed by the "_" character within the contents of a field?

3 Answers3