")[1] as-6ren">
gpt4 book ai didi

sql - 将单个sql列拆分为五个

转载 作者:行者123 更新时间:2023-12-04 20:29:47 24 4
gpt4 key购买 nike

我试图围绕“>”定界符将一列分成最多五列,但我尝试过的东西没有成功:

我试过了

select
id,
compoundColumn,
split(compoundColumn," > ")[1] as "first"
split(compoundColumn," > ")[2] as "second"
from table
where compoundColumn is not null

没有效果,

这有点做了(无论如何是第一部分,而不是第 n 部分)

select
id,
compoundColumn,
first(split(compoundColumn," > ")) as "first"
nth(compoundColumn," > ")[n] as "second"
from table

我在这里找到了很多例子,但他们似乎都在说要使用括号,但括号会引发错误:

Exception: Malformed SQL. More information: Error with SQL statement: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[1] as "first" from table where compoundColumn IS NOT NULL' at line 3.

最佳答案

  • 您的 SQL 中“first”后缺少逗号
  • 我猜 CloudSQL 是基于某些旧版本的 MySQL,它只能使用 substring_index 进行拆分(请参阅下面的查询 - 是的,它冗长笨拙,case 子句必须清理短字符串)
  • 也许尝试使用 [offset(0)][ordinal(1)] 括号,这对我们有用,尽管我们使用 Postgres 方言,也作为 # standardSql,不是#legacySql

来自第二点的 SQL:( fiddle )

select id,
case when substring_index(cc,' > ',0) = cc then null else substring_index(substring_index(cc,' > ',1),' > ',-1) end as a1,
case when substring_index(cc,' > ',1) = cc then null else substring_index(substring_index(cc,' > ',2),' > ',-1) end as a2,
case when substring_index(cc,' > ',2) = cc then null else substring_index(substring_index(cc,' > ',3),' > ',-1) end as a3,
case when substring_index(cc,' > ',3) = cc then null else substring_index(substring_index(cc,' > ',4),' > ',-1) end as a4,
case when substring_index(cc,' > ',4) = cc then null else substring_index(substring_index(cc,' > ',5),' > ',-1) end as a5
from d

关于sql - 将单个sql列拆分为五个,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/57152806/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com