拆分字符串 Teradata SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23639344/
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
Split String Teradata SQL
提问by cloud36
I'm looking to split a string in Teradata.
我想在 Teradata 中拆分一个字符串。
The table might look something like this.
该表可能看起来像这样。
column1
hello:goodbye:afternoon
I'm trying to use SUBSTRING and INSTR to extract specific words. So, say I want to select "goodbye". I'm trying the following query.
我正在尝试使用 SUBSTRING 和 INSTR 来提取特定的单词。所以，说我想选择“再见”。我正在尝试以下查询。
SELECT SUBSTRING(a.column1 from index(a.column1,':')+1 for INSTR(a.column1,':',0,2))
FROM db.table as a
I get the following error.
我收到以下错误。
SELECT Failed. [3707] Syntax error, expected something like ')' between the word 'INSTR' and '('
I'm not sure why I'm getting that error. It lets me use INDEX to deduce a number in place of INSTR, so I'm not sure why it is acting this way when I use INSTR.
我不确定为什么会出现该错误。它让我可以使用 INDEX 来推断出一个数字来代替 INSTR，所以我不确定为什么当我使用 INSTR 时它会以这种方式行事。
回答by dnoeth
If this was TD14 you wouldn't need INSTR, there's a STRTOKfunction :-)
如果这是 TD14，你就不需要了INSTR，有一个STRTOK功能:-)
STRTOK(column1,':',2),
For earlier releases it's
对于较早的版本，它是
CASE
WHEN column1 LIKE '%:%:%'
THEN SUBSTRING(column1 FROM POSITION(':' IN column1) + 1 FOR POSITION(':' IN
SUBSTRING(column1 FROM POSITION(':' IN column1) + 1)) - 1)
WHEN column1 LIKE '%:%'
THEN SUBSTRING(column1 FROM POSITION(':' IN column1) + 1)
END
The CASE LIKEist just to prevent an "string subscript out of bound" error when there no colon.
这CASE LIKE只是为了在没有冒号时防止“字符串下标越界”错误。
