postgresql 我们可以在 SUBSTRING 中使用 CASE...WHEN 吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23936319/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can we use a CASE...WHEN inside SUBSTRING?
提问by Harry Lime
(Here's a much simpler case of my actual conundrum at work)
（这是我在工作中遇到的实际难题的一个简单得多的案例）
Let's say I have a table, called 'a', with a column named 'col' with the following values (say a column of length 2 with many random combination of characters):
假设我有一个名为“a”的表，其中包含一个名为“col”的列，其中包含以下值（比如长度为 2 且具有许多随机字符组合的列）：
col A3 D2 @5 #) ...
I want to use a select statement that outputs two columns called 'letter' and 'number' where 'letter' is the first character of 'col' & 'number' is the second character of 'col', but with the following mapping:
我想使用一个 select 语句，它输出两列名为“letter”和“number”的列，其中“letter”是“col”的第一个字符，“number”是“col”的第二个字符，但具有以下映射：
If substring(col FROM 1 for 1) in ('!','@','#'),
then letter = 'A' and 'number' = substring(col FROM 2 for 1)
(i.e., if the first character of something in 'col' is '!', '@', or '#', map it to 'letter' as 'A' while keeping the second character of 'col' the same and mapping that value to 'number')
（即，如果 'col' 中某事物的第一个字符是 '!'、'@' 或 '#'，则将其映射到 'letter' 为 'A'，同时保持 'col' 的第二个字符相同并映射该值到“数字”）
If col = '%9',
then 'letter' = 'H' and 'number' = '9'
(i.e., if a specific value in 'col' is '%9', then map it to 'letter' as 'H' and 'number' as '9')
（即，如果 'col' 中的特定值是 '%9'，则将其映射到 'letter' 为 'H' 和 'number' 为 '9'）
If substring(col FROM 2 for 1) = '4',
then 'letter' = substring(col FROM 1 for 1) and 'number' = '7'
(i.e., if the second character of a value in 'col' is '4', leave the first character unchanged and map it to 'letter' and map the second character to 'number' as '7')
（即，如果 'col' 中某个值的第二个字符是 '4'，则保持第一个字符不变并将其映射到 'letter' 并将第二个字符映射到 'number' 为 '7'）
Lastly, only select values where 'letter' is a letter and 'number' is a one character number. That is,
最后，仅选择“字母”是字母且“数字”是单字符数字的值。那是，
'letter' in ('A','B',...'Z') and 'number' in ('0','1',...'9')
What query would I run to solve this? That is, with the mapping hardcoded using CASE..WHEN?
我会运行什么查询来解决这个问题？也就是说，使用 CASE..WHEN 对映射进行硬编码？
Ideally, I'm trying to do something like:
理想情况下，我正在尝试执行以下操作：
SELECT substring((Case...When) FROM 1 for 1) AS letter,
substring((Case...When) FROM 2 for 1) AS number
FROM a;
Thanks!
谢谢！
采纳答案by rs.
Try this
试试这个
SELECT * FROM (
SELECT
CASE
WHEN SUBSTRING(COL from 1 for 1) IN ('!','@','#') THEN 'A'
WHEN COL LIKE '%9' THEN 'H'
WHEN SUBSTRING(COL,2,1) = '4' THEN SUBSTRING(COL,1)
END Letter,
CASE
WHEN SUBSTRING(COL from 1 for 1) IN ('!','@','#') THEN substring(col FROM 2 for 1)
WHEN COL LIKE '%9' THEN '9'
WHEN SUBSTRING(COL,2,1) = '4' THEN '7'
END Number
FROM A
)
WHERE (Letter ~ '^[A-Za-z]$') = true AND (Number ~ '^[0-9]$') = true;
