如何过滤将参数传递给将过滤掉字符的substring()? [英] How do I filter pass an argument to substring() that will filter out a character?
问题描述
我试图修改的列示例:
Example of Column I'm trying to modify:
| Column 1 |
asdf sixsix =IWantToReturnThisIn1Column; asdfasd seven12=IWantToReturnThisInColumn2
我想捕获=和之间的内容;对于col1a并捕获第二个右侧的所有内容=第2列。
新秀问题,我很确定但是它让我很难过。
我想要的输出看起来如下:
I want to capture the contents between = and ; for col1a and capture everything to the right of the second = in col 2.
Rookie question, I'm sure but it's stumping me.
My desired out put would look as follows:
| Col1 | Col2 |
IWantToReturnThisIn1Column | IWantToReturnThisInColumn2 |
我的尝试:
SELECT
col1 = substring(d.column1,13,20)
What I have tried:
SELECT
col1 = substring(d.column1,13,20)
推荐答案
SELECT
SUBSTRING(col1, 14, 26) AS Col1,
SUBSTRING(col2, 17, 26) AS Col2
FROM col
此查询将带来所需的结果。
查询结果:
This query will bring the desired result.
Query Result:
Col1 Col2
IWantToReturnThisIn1Column IWantToReturnThisInColumn2
在=和;之间的情况下,使用CHARINDEX也可以实现上述目的。对于不同的行是必需的。
Above can also be achieved by using CHARINDEX in case straing between "=" and ";" is required for different rows.
SELECT
SUBSTRING(Col1, (CHARINDEX('=', col1, 1) + 1), (CHARINDEX(';', col1) - (CHARINDEX('=', col1, 1) + 1))),
SUBSTRING(Col2, (CHARINDEX('=', col2, 1) + 1), (LEN(col2) - (CHARINDEX('=', col2, 1))))
FROM col
I希望这能满足要求。
I hope this will fulfill the requirement.
我猜文本长度是可变的,所以你需要构建一个复杂的公式。
我让你处理细节,比如声明中间变量或者在没有公式的大公式中组合所有这些。
I guess the text length is variable, so you need to build a little complicated formula.
I let you deal with details like declaring the intermediate variables or combining all this in large formulas without formulas.
SC= INSTR(d.column1, ";") -- position of ';'
EQ1= INSTR(d.column1, "=") -- position of first '='
Col1= SUBSTRING(d.column1, EQ1+1, SQ-EQ1-1)
EQ2= INSTR(d.column1, "=", SC) -- position of second '='
Col2= SUBSTRING(d.column1, EQ2+1)
这篇关于如何过滤将参数传递给将过滤掉字符的substring()?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!