oracle 子串的巧妙使用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4192517/
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
Tricky use of substring
提问by Shaz
My question is about a field called contract_nm varchar2(14). I need to get 3 different values from it use the field to get the filter for a where clause.
我的问题是关于一个名为 contract_nm varchar2(14) 的字段。我需要从中获取 3 个不同的值，使用该字段来获取 where 子句的过滤器。
Here is the sample data and how long the data might be, either 9 or 10 or 11, no more for now it can be.
这是示例数据以及数据可能有多长，9 或 10 或 11，现在不能了。
CONTRACT_NM LENGTH(CONTRACT_NM)
F.US.WZ10 9
F.US.WZ11 9
F.US.WZ12 9
F.US.RBZ10 10
F.US.RBZ11 10
F.US.RBZ12 10
F.US.ZWAZ10 11
F.US.ZWAZ11 11
F.US.ZWAZ12 11
etc
1) I need to display the last 3 characters of contract_nm.
1）我需要显示contract_nm的最后3个字符。
2) Check the last 3 characters of contract_nm to see if the first letter is one of the below, then it would be that MONTH and the YEAR would be the next 2 letters and the DAY would be defaulted to the first day of the month. I need to display like a date because it is going into a date field.
2）查看contract_nm的最后3个字符，看第一个字母是否是下面的一个，那么接下来的2个字母就是MONTH和YEAR，DAY默认为当月的第一天。我需要像日期一样显示，因为它要进入日期字段。
Trade Months (Terms):
F January
G February
H March
J April
K May
M June
N July
Q August
U September
V October
X November
Z December
3) This is a bit difficult to explain in writing, I will try and hope you understand me!!! Read contract_nm data, ignore the first five characters (F.US.), then ignore the last 3 characters i.e. Z11. Now, what we are working with is either 1 character (9 length) or 2 characters (10 length) or 3 characters (11 length) of data in the middle, always, see the below data. If 1 character, then display that letter, else if 2 characters, then check the 2nd letter if is not ‘E' or ‘A' or ‘T' then display both letters, else display just the 1st letter. Else if 3 characters, then display the first 2 letters.
3）这有点难以用文字解释，我会努力希望你理解我！！！读取contract_nm 数据，忽略前5 个字符（F.US.），然后忽略后3 个字符即Z11。现在，我们正在处理的是中间的 1 个字符（9 个长度）或 2 个字符（10 个长度）或 3 个字符（11 个长度）的数据，请参见下面的数据。如果是 1 个字符，则显示该字母，如果是 2 个字符，则检查第二个字母，如果不是 'E' 或 'A' 或 'T'，则显示两个字母，否则只显示第一个字母。否则如果是 3 个字符，则显示前 2 个字母。
4) I need a filter for my where clause to read contract_nm data, ignore the first five characters (F.US.), then ignore the last 3 characters i.e. Z11. Now, what we are working with is either 1 character (9 length) or 2 characters (10 length) or 3 characters (11 length) of data in the middle, always, see the below data. If 2 characters, then check the 2nd letter if is not ‘E' or ‘T' then don't pull the data, else do it. Else if 3 characters, then check the 3rd letter if is not ‘E' or ‘T' then don't pull the data, else do it.
4）我的 where 子句需要一个过滤器来读取 contract_nm 数据，忽略前五个字符（F.US.），然后忽略最后 3 个字符，即 Z11。现在，我们正在处理的是中间的 1 个字符（9 个长度）或 2 个字符（10 个长度）或 3 个字符（11 个长度）的数据，请参见下面的数据。如果是 2 个字符，则检查第二个字母，如果不是 'E' 或 'T'，则不要提取数据，否则执行。否则，如果是 3 个字符，则检查第 3 个字母，如果不是 'E' 或 'T'，则不要提取数据，否则执行。
回答by Bob Jarvis - Reinstate Monica
Try the following:
请尝试以下操作：
Question 1:
问题 1：
contract_nm_month_year := substr(contract_nm, -3);
Question 2:
问题2：
contract_date := CASE substr(contract_nm, -3, 1)
WHEN 'F' THEN TO_DATE('01-JAN-' || SUBSTR(contract_nm, -2), 'DD-MON-RR');
WHEN 'G' THEN TO_DATE('01-FEB-' || SUBSTR(contract_nm, -2), 'DD-MON-RR');
WHEN 'H' THEN TO_DATE('01-MAR-' || SUBSTR(contract_nm, -2), 'DD-MON-RR');
WHEN 'J' THEN TO_DATE('01-APR-' || SUBSTR(contract_nm, -2), 'DD-MON-RR');
WHEN 'K' THEN TO_DATE('01-MAY-' || SUBSTR(contract_nm, -2), 'DD-MON-RR');
WHEN 'M' THEN TO_DATE('01-JUN-' || SUBSTR(contract_nm, -2), 'DD-MON-RR');
WHEN 'N' THEN TO_DATE('01-JUL-' || SUBSTR(contract_nm, -2), 'DD-MON-RR');
WHEN 'Q' THEN TO_DATE('01-AUG-' || SUBSTR(contract_nm, -2), 'DD-MON-RR');
WHEN 'U' THEN TO_DATE('01-SEP-' || SUBSTR(contract_nm, -2), 'DD-MON-RR');
WHEN 'V' THEN TO_DATE('01-OCT-' || SUBSTR(contract_nm, -2), 'DD-MON-RR');
WHEN 'X' THEN TO_DATE('01-NOV-' || SUBSTR(contract_nm, -2), 'DD-MON-RR');
WHEN 'Z' THEN TO_DATE('01-DEC-' || SUBSTR(contract_nm, -2), 'DD-MON-RR');
ELSE NULL;
END;
Question 3:
问题 3：
display_3 := CASE length(contract_nm)
WHEN 9 THEN SUBSTR(contract_nm, 6, 1);
WHEN 10 THEN
CASE SUBSTR(contract_nm, 7, 1)
WHEN 'E' THEN SUBSTR(contract_nm, 6, 1);
WHEN 'A' THEN SUBSTR(contract_nm, 6, 1);
WHEN 'T' THEN SUBSTR(contract_nm, 6, 1);
ELSE SUBSTR(contract_nm, 6, 2);
END;
WHEN 11 THEN SUBSTR(contract_nm, 6, 2)
END;
Question 4:
问题 4：
WHERE CASE length(contract_nm)
WHEN 9 THEN 0 -- never pull data for contract length = 9
WHEN 10 THEN
CASE SUBSTR(contract_nm, 7, 1)
WHEN 'E' THEN 1
WHEN 'T' THEN 1
ELSE 0
END
WHEN 11 THEN
CASE SUBSTR(contract_nm, 8, 10
WHEN 'E' THEN 1
WHEN 'T' THEN 1
ELSE 0
END
ELSE 0
END = 1;
Share and enjoy.
分享和享受。
回答by Dave Costa
Focus on how to break CONTRACT_NM up into the pieces you need. Then create a view so that you can treat each piece as a column. Applying your various conditions and transformations should then be straightforward.
专注于如何将 CONTRACT_NM 分解成您需要的部分。然后创建一个视图，以便您可以将每个部分视为一列。应用您的各种条件和转换应该很简单。
It appears you are always ignoring the first 5 characters, so just chop them out.
看起来你总是忽略前 5 个字符，所以把它们去掉。
SELECT SUBSTR( contract_nm, 5, LENGTH(contract_nm)-8 ) flags,
SUBSTR( contract_nm, -3, 1 ) month_flag,
SUBSTR( contract_nm, -2 ) year
FROM table
1) month_flag||year.
2)
1） month_flag||year。
2)
TO_DATE(
CASE month_flag WHEN 'F' THEN 'JAN' WHEN 'G' THEN 'FEB' ... END || year,
'MONRR'
)
3)
3)
CASE WHEN LENGTH(flags) = 3
THEN SUBSTR(flags,1,2)
WHEN LENGTH(flags) = 2 AND SUBSTR(flags,2,1) NOT IN ('E','A','T')
THEN flags
ELSE SUBSTR(flags,1,1)
4) Very similar to #3
4) 与#3 非常相似
回答by Tony Andrews
1) I need to display the last 3 characters of contract_nm.
1）我需要显示contract_nm的最后3个字符。
Use SUBSTR(contract_nm, -3)
使用 SUBSTR(contract_nm, -3)
Re Q2, use a CASE expression something like:
关于 Q2，请使用 CASE 表达式，例如：
CASE SUBSTR(contract_nm, -3, 1)
WHEN 'F' THEN TO_DATE('JAN'||SUBSTR(contract_nm, -2), 'MONRR')
WHEN 'G' THEN TO_DATE('FEB'||SUBSTR(contract_nm, -2), 'MONRR')
...etc.
END
