主页 / coding / 问题 / 79493994
Accepted
elbarna
Asked: 2025-03-08 14:35:15 +0800 CST

Mysql：可能存在“选择性”子字符串函数吗？

  • 772

情况就是这样

select calldate,src,dst,clid,nome from cdr limit 255 offset 1015;
+---------------------+------------+--------------+------------------------------------+------+
| calldate            | src        | dst          | clid                               | name |
+---------------------+------------+--------------+------------------------------------+------+
| 2025-03-04 04:49:59 | 1234567891 | +11123456789 | NONE                               | CKTC |
| 2025-03-04 04:49:59 | 1234567891 | +11123456789 | NONE                               | CKTC |
| 2025-03-04 04:49:59 | 1234567891 | +11123456789 | NONE                               | CKTC |
| 2025-03-04 11:02:07 | 9812737374 | +11123456789 | NONE                               | CKTC |
| 2025-03-04 11:02:05 | 9812737374 | +11123456789 | NONE                               | CKTC |
| 2025-03-04 11:02:05 | 9812737374 | +11123456789 | NONE                               | CKTC |
| 2025-03-04 11:31:53 | 9812737374 | +11123456789 | NONE                               | CKTC |
| 2025-03-04 16:22:08 | 3233333    | 9182374747   | NONE                               | CKTC |
| 2025-03-04 18:41:46 | 3233333    | 9182374747   | NONE                               | CKTC |
| 2025-03-08 01:11:27 | 3233333    | 9182374747   | NONE                               | CKTC |
+---------------------+------------+--------------+------------------------------------+------+

我想用空值替换 +11，简而言之：我想将其剪切掉但只替换 +11，而不触及其他值。

如果我用这行

select calldate,src,substr(dst,4,11) as dst,clid,nome from cdr limit 255 offset 1015;

它削减了所有值，而不仅仅是 +11

如果我用这个

select calldate,src,(select substr(dst,4,11) where dst='+11123456789') as dst,clid,nome from cdr limit 255 offset 1015;

工作正常，但其他数字的“dst”值变为NULL我的想法是这样的

select calldate,src,dst,clid,nome from cdr limit 255 offset 1015;
    +---------------------+------------+--------------+------------------------------------+------+
    | calldate            | src        | dst          | clid                               | name |
    +---------------------+------------+--------------+------------------------------------+------+
    | 2025-03-04 04:49:59 | 1234567891 | 123456789    | NONE                               | CKTC |
    | 2025-03-04 04:49:59 | 1234567891 | 123456789    | NONE                               | CKTC |
    | 2025-03-04 04:49:59 | 1234567891 | 123456789    | NONE                               | CKTC |
    | 2025-03-04 11:02:07 | 9812737374 | 123456789    | NONE                               | CKTC |
    | 2025-03-04 11:02:05 | 9812737374 | 123456789    | NONE                               | CKTC |
    | 2025-03-04 11:02:05 | 9812737374 | 123456789    | NONE                               | CKTC |
    | 2025-03-04 11:31:53 | 9812737374 | 123456789    | NONE                               | CKTC |
    | 2025-03-04 16:22:08 | 3233333    | 9182374747   | NONE                               | CKTC |
    | 2025-03-04 18:41:46 | 3233333    | 9182374747   | NONE                               | CKTC |
    | 2025-03-08 01:11:27 | 3233333    | 9182374747   | NONE                               | CKTC |
    +---------------------+------------+--------------+------------------------------------+------+

，怎么办？可能吗？

mysql

1 个回答

  1. Best Answer

    使用REPLACE()函数：

    SELECT
    calldate,
    src,
    REPLACE(dst, '+11', '') AS dst,
    clid,
    nome
FROM cdr
LIMIT 255
OFFSET 1015;

    +11如果您想要更精确、更具体定位列中的前导dst，那么请使用正则表达式替换：

    SELECT
    calldate,
    src,
    REGEXP_REPLACE(dst, '^\\+11', '') AS dst,
    clid,
    nome
FROM cdr
LIMIT 255
OFFSET 1015;
    • 1

相关问题