CODEWITHSUNDEEP
mysqlsubstringdelimiter
user7128548
user7128548

Reputation:

How to select string between delimiters and insert into other column in MySQL?

Let's say I have a table in a MySQL database with two columns first_string and second_string.

first_string contains this sentence: Hello! This is my first test. I like this test.

I want to get all between the delimiters my and test (including the delimiters itself) and insert it in second_string.

So second_string should be: my first test.

It's important to take the first available delimiters: I need my first test and not my first test. I like this test.

This is what I have so far:

SELECT SUBSTRING(@Text, CHARINDEX('my', @Text), CHARINDEX('test',@text) - CHARINDEX('my', @Text) + Len('test'));

But I don't know how to do the rest. And will that code always look for the first available delimiter?

I hope anybody can help me. Thanks!

Upvotes: 2

Views: 318

Answers (1)

Bernd Buffen
Bernd Buffen

Reputation: 15057

Do you mean some thing like this ?

mysql> SET @Text='Hello! This is my first test. I like this test.'; Query OK, 0 rows affected (0,00 sec)

SET @Text='Hello! This is my first test. I like this test.';

SELECT
    SUBSTRING(@Text, INSTR(@Text,'my')
    , (INSTR(@Text,'test') - INSTR(@Text,'my') + CHAR_LENGTH('test')))
    INTO @result;

SELECT @result;

Sample

    mysql> SET @Text='Hello! This is my first test. I like this test.';
    mysql> SELECT
        ->     SUBSTRING(@Text, INSTR(@Text,'my')
        ->     , (INSTR(@Text,'test') - INSTR(@Text,'my') + CHAR_LENGTH('test')))
        ->     INTO @result;
    Query OK, 1 row affected (0,00 sec)

    mysql> SELECT @result;
    +---------------+
    | @result       |
    +---------------+
    | my first test |
    +---------------+
    1 row in set (0,00 sec)

    mysql>

Upvotes: 1

Related Questions