如何过滤将参数传递给将过滤掉字符的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)

好​​tuto网站：

SQL教程 [ ^ ]

这篇关于如何过滤将参数传递给将过滤掉字符的substring（）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！