mysq请求|从表中的字符串获取数字

nhjlsmyf 于 2021-06-23 发布在 Mysql

关注(0)|答案(2)|浏览(259)

我有一个产品表（products），上面有记录：

id name
1  1969-1972 Chevrolet Small Block 
2  1971-1975 Chevrolet Small Block Fan Shroud
3  1964 Chevrolet Bumper Kit Complete
4  Chevrolet Clutch And Brake Pedal Assembly
...

现在我想让request end提取前四个数字作为开始日期，从6到9的字符作为结束日期。
因此结果表将是：

id name                                             start end
 1  1969-1972 Chevrolet Small Block                 1969  1972
 2  1971-1975 Chevrolet Small Block Fan Shroud      1971  1975
 3  1964 Chevrolet Bumper Kit Complete              1964  null
 4  Chevrolet Clutch And Brake Pedal Assembly       null  null     
 ...

谢谢！

mysql php

来源：https://stackoverflow.com/questions/50534712/mysq-request-get-numbers-from-string-in-table

2条答案

按热度按时间

xuo3flqw1#

1. LEFT(name , 4) as start; 
 2. CAST(SUBSTRING(name, 6, 4) AS UNSIGNED) as end;

赞(0）回复(0）举报 2021-06-23

mlnl4t2r2#

您可以使用一些regexp匹配使查询更加健壮。

SELECT id, 
  name,
  CASE WHEN name REGEXP '^[[:digit:]]{4}[ -]' THEN CAST(LEFT(name, 4) AS UNSIGNED)
  ELSE NULL
  END AS start,
  CASE WHEN name REGEXP '^[[:digit:]]{4}-[[:digit:]]{4} ' THEN CAST(SUBSTRING(name, 6, 4) AS UNSIGNED)
  ELSE NULL
  END AS end
FROM products

输出：

id  name                                        start   end
1   1969-1972 Chevrolet Small Block             1969    1972
2   1971-1975 Chevrolet Small Block Fan Shroud  1971    1975
3   1964 Chevrolet Bumper Kit Complete          1964    (null)
4   Chevrolet Clutch And Brake Pedal Assembly   (null)  (null)

或者，如果您想：

SELECT id, 
  CASE WHEN name REGEXP '^[[:digit:]]{4}-[[:digit:]]{4} ' THEN SUBSTRING(name, 10)
       WHEN name REGEXP '^[[:digit:]]{4} ' THEN SUBSTRING(name, 6)
       ELSE name 
  END AS name,
  CASE WHEN name REGEXP '^[[:digit:]]{4}[ -]' THEN CAST(LEFT(name, 4) AS UNSIGNED)
  ELSE NULL
  END AS start,
  CASE WHEN name REGEXP '^[[:digit:]]{4}-[[:digit:]]{4} ' THEN CAST(SUBSTRING(name, 6, 4) AS UNSIGNED)
  ELSE NULL
  END AS end
FROM products

输出：

id  name                                        start   end
1   Chevrolet Small Block                       1969    1972
2   Chevrolet Small Block Fan Shroud            1971    1975
3   Chevrolet Bumper Kit Complete               1964    (null)
4   Chevrolet Clutch And Brake Pedal Assembly   (null)  (null)

赞(0）回复(0）举报 2021-06-23

我来回答

mysq请求|从表中的字符串获取数字

2条答案

相关问题

热门标签

最新问答