如何从字符串中选择由 coma(",") 分隔的随机子字符串 [英] How to select Random Sub string,which seperated by coma(",") From a string

问题描述

我有一个列，其中包含一个由昏迷分隔的 ID 列表，我想从中选择一个随机 ID，

<前>滑动 |广告ID__________________________1 |1、3、5、6、8、102 |2,3,4,6,8,9,11

我想从上面的表格中选择一个随机的 adsId.

预期结果:

<前>滑动 |广告ID_________________1 |52 |11

提前致谢.

解决方案

请注意，最好不要在字段中存储逗号分隔值.但是如果你不能改变你的数据库结构，你可以使用这个 MySQL 查询:

SELECT幻灯片，SUBSTRING_INDEX(SUBSTRING_INDEX(adsId, ',', rand()*(LENGTH(adsId)-LENGTH(REPLACE(adsID, ',', '')))+1),',',-1) 作为 random_ads从你的桌子

请参阅 fiddle 此处.

I have a column which having a list of id's separated by coma, I want to select a random ID from it,

    slId  |  adsId
    __________________________
     1    |   1,3,5,6,8,10
     2    |   2,3,4,6,8,9,11

I want to select one Random adsId from above Table.

Expected Result:

    slId  |  adsId
    _________________
     1    |   5
     2    |   11

Thanks In Advance.

解决方案

Please notice that it's always better not to store comma separated values in your fields. But if you can't change your database structure, you could use this MySQL query:

SELECT
  slID,
  SUBSTRING_INDEX(
    SUBSTRING_INDEX(adsId, ',', rand()*(LENGTH(adsId)-LENGTH(REPLACE(adsID, ',', '')))+1),
    ',',
    -1) as random_ads
FROM
  yourtable

Please see fiddle here.

这篇关于如何从字符串中选择由 coma(",") 分隔的随机子字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！