doubi1931
2015-08-17 14:18
浏览 97
已采纳

MySQL:在特定分隔符和下一个非唯一分隔符之间返回SUBSTR

I'm trying to return a SUBSTR between two delimiters. The Problem is, that the second delimiter is non-unique in the whole string.

Example:

testurl.de/some:text/&[uniquedelimiter]=texttoextract:sometext:someothertext

So I want to extract the string between "&[uniquedelimiter]=" and the next ":" after the unique delimiter. The occurrence of ":" in the string is variable.

Right now I'm using a double SUBSTRING_INDEX function, but I get errors if the string contains more than one ":".

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(list_of_urls, '&[uniquedelimiter]=',-1),':')
FROM url_table;

I couldn't find a comparable solution on any Community.

Thank you so much for your help, Lars

图片转代码服务由CSDN问答提供 功能建议

我正在尝试在两个分隔符之间返回一个SUBSTR。 问题是,第二个分隔符在整个字符串中是非唯一的。

示例:

testurl.de/some:text/& ; [uniquedelimiter] = texttoextract:sometext:someothertext

所以我想在“& [uniquedelimiter] =”和 next “之间提取字符串:” 在唯一分隔符之后。 字符串中“:”的出现是可变的。

现在我正在使用双SUBSTRING_INDEX函数,但如果字符串包含多个,则会出错 一个“:”。

  SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(list_of_urls,'& [uniquedelimiter] =', -  1),':')
FROM url_table; 
 <  / code>  
 
 

我在任何社区都找不到类似的解决方案。

非常感谢你的帮助, Lars

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • duanfu1945 2015-08-17 14:32
    已采纳

    This seems to be working, maybe you can sort it, not sure:

    SELECT
    SUBSTRING(
        url,
        (POSITION('&[uniquedelimiter]=' IN url) + LENGTH('&[uniquedelimiter]=')),
        LOCATE(':', url, (POSITION('&[uniquedelimiter]=' IN url) + LENGTH('&[uniquedelimiter]='))) -
        (POSITION('&[uniquedelimiter]=' IN url) + LENGTH('&[uniquedelimiter]='))
    )
    from bla
    

    My strategy was like this:

    SET @url                   = (SELECT url from bla limit 1);
    SET @pos_uniq_deli_start   = POSITION('&[uniquedelimiter]=' IN @url);
    SET @uniq_deli_length      = LENGTH('&[uniquedelimiter]=');
    SET @pos_uniq_deli_end     = @pos_uniq_deli_start + @uniq_deli_length;
    --                           LOCATE(char, field, offset)
    SET @pos_colon_after_deli  = LOCATE(':', @url, @pos_uniq_deli_end);
    SET @delta_unqi_del_colon  = @pos_colon_after_deli - @pos_uniq_deli_end;
    
    --            SUBSTRING(field, start_pos, length)
    SET @result = SUBSTRING(@url,@pos_uniq_deli_end, @delta_unqi_del_colon);
    SELECT @result;
    
    点赞 打赏 评论

相关推荐 更多相似问题