CODEWITHSUNDEEP
mysqlsql
prawirs
prawirs

Reputation: 60

How to search for substring preceded by a known substring

Using SQL, I want to search for and retrieve sub-strings that are preceded by a known sub-string "XX." and ending in a " " or "'".

For example if I start with CONTOOOH 788 XX. 3C, MNOP I need to extract value 3C

I've tried with substring(input, posisiton, len) but not sure about criteria of len since the len are vary.

select substring(input, position("XX." in input)+4, **???**)    
from tables 
where input like '%XX.%';

I am using MySQL.

enter image description here

Input
CONTOH LALALA 12 XX. 1 ABCD LALA NANA MAMA KAKA
CONTOH NANANANA 34 XX. 02 EFGH IJKL MN
CONTOOOH MAMAMA XX. 1A IJKL YOYO
CONTOOOH NANA XIXI 788 XX. 423C, MNOP QRSTU ASDF POIU
EXAMPLE BLA BLA HOHOHO 910 XX. A4, QRST ASDGHH
EXAMPLE ZZZ AAA BBB 1112 XX. BB5, UVWXASDGHH


Output
1
02
1A
423C
A4
BB5

Upvotes: 2

Views: 98

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

One option uses SUBSTRING_INDEX with REPLACE:

SELECT
    Input,
    REPLACE(SUBSTRING_INDEX(
        SUBSTRING_INDEX(Input, 'XX. ', -1), ' ', 1), ',', '') AS Output
FROM yourTable;

enter image description here

Demo

Here is how the string operations are working, step by step

CONTOOOH 788 XX. 3C, MNOP    - initial input
3C, MNOP                     - after first call to SUBSTRING_INDEX
3C,                          - after second call to SUBSTRING_INDEX
3C                           - after call to REPLACE, to remove the comma

Upvotes: 1

awiebe
awiebe

Reputation: 3836

According to the documentation for MySQL substring

length Optional. The forms without a len argument return a substring from string str starting at position pos. https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_substring

Thus if your current code works to your liking except for length, simply omit it, and it will return the entire substring.

Upvotes: 0

Related Questions