I have been searching now for hours.
Got a String like: "_1_11_19_12_17_21_41_111_100_1"
Now, I want the Output to be a single 1
When I try:
"SELECT * FROM jobs_tb WHERE position_id LIKE '%1%' "
This prints all most everything:
I tried:
SELECT *
FROM `jobs_tb`
WHERE (
`position_id` LIKE '%_1%'
)
OR (
`position_id` LIKE '%_1_%'
)
All most the same as the previous.
How do you isolate a 1 when 1s are mixed with other numbers?
Any suggestion is highly welcomed
Note that an underscore is a single character wildcard, just as the percent sign is a multicharacter wildcard, so if you want to match a literal underscore, you'll have to escape it. Also, the string can start or end with a '1', so you need to take that into account. A simple trick is to add extra underscores around the value to search in. Try this query:
SELECT *
FROM `jobs_tb`
WHERE
CONCAT('_', `position_id`, '_') LIKE '%\_1\_%'
CREATE FUNCTION PositionIdStringContains(positionIdText TEXT, digit TEXT) RETURNS BIT
BEGIN
DECLARE digitStart TEXT;
SET digitStart = CONCAT( digit, '\_%' );
DECLARE digitEnd TEXT;
SET digitEnd = CONCAT( '%\_', digit );
DECLARE digitMid TEXT;
SET digitMid = CONCAT( '%\_', digit, '\_%' );
RETURN
positionIdText LIKE digit OR
positionIdText LIKE digitStart OR
positionIdText LIKE digitMid OR
positionIdText LIKE digitEnd
END;
Then use it in your SELECT statements like so:
SELECT
*
FROM
jobs_tb
WHERE
PositionIdStringContains( position_id, '1' )
; delimiter. You will need to configure your MySQL client accordingly. That's not something I can help you with.CREATE FUNCTION stores the function in the database permanently.
CREATE FUNCTION) so you don't have to repeat the logic in other statements.WHEREclause (see my answer).