SQL实现单元格内01字符串拆分到独立列的方法问询

以下方案基于权限字符串长度固定的前提（示例为5位长度），如果字符串长度不固定可参考末尾的动态SQL思路。

MySQL #

SELECT
  -- 如需保留原完整权限字符串，可取消下一行注释
  -- Column1 AS original_permission,
  SUBSTRING(Column1, 1, 1) AS Column1,
  SUBSTRING(Column1, 2, 1) AS Column2,
  SUBSTRING(Column1, 3, 1) AS Column3,
  SUBSTRING(Column1, 4, 1) AS Column4,
  SUBSTRING(Column1, 5, 1) AS Column5
FROM 你的表名;

PostgreSQL #

SELECT
  SUBSTR(Column1, 1, 1) AS Column1,
  SUBSTR(Column1, 2, 1) AS Column2,
  SUBSTR(Column1, 3, 1) AS Column3,
  SUBSTR(Column1, 4, 1) AS Column4,
  SUBSTR(Column1, 5, 1) AS Column5
FROM 你的表名;

SQL Server #

SELECT
  SUBSTRING(Column1, 1, 1) AS Column1,
  SUBSTRING(Column1, 2, 1) AS Column2,
  SUBSTRING(Column1, 3, 1) AS Column3,
  SUBSTRING(Column1, 4, 1) AS Column4,
  SUBSTRING(Column1, 5, 1) AS Column5
FROM 你的表名;

Oracle #

SELECT
  SUBSTR(Column1, 1, 1) AS Column1,
  SUBSTR(Column1, 2, 1) AS Column2,
  SUBSTR(Column1, 3, 1) AS Column3,
  SUBSTR(Column1, 4, 1) AS Column4,
  SUBSTR(Column1, 5, 1) AS Column5
FROM 你的表名;

不定长字符串处理思路 #

如果权限字符串长度不固定，可先查询字段的最大长度，再通过动态SQL拼接所有位置的截取逻辑，无需手动编写每一列的查询语句。

内容的提问来源于stack exchange，提问作者Paul Kalathas