问题

我正在使用一个过程来重命名表名，这个过程运行得很好。SPX处理了所有键和约束的事情，这方面也做得很好。我的唯一问题是，我正在使用替换来改变表名，但问题是如果列的名称与表名相同，它也会更改那个名称，这就是代码失败的地方

这是一个示例

create Table dbo.States(
int int not null identity, 
States nvarchar(100))

所以如果我将 States 改为 Provinces，它也会重命名列，这是我不想要的，

我在这样使用它

Replace(mytableString, 'States', 'provinces','all')

create Table dbo.States(
int int not null identity, 
States nvarchar(100))

Replace(mytableString, 'States', 'provinces','all')

答案1

得分: 1

I do not know ColdFusion but I can suggest you a Java solution. You can use the regex, States(?=\() to match the table name, States and replace it with the desired string e.g. provinces. Check this to see a demo and explanation of the regex.

Java code:

public class Main {
    public static void main(String[] args) {
        String sql = "create Table dbo.States(\n" + 
                        "int int not null identity, \n" + 
                        "States nvarchar(100))";

        String regex = "States(?=\\()";

        String result = sql.replaceAll(regex, "provinces");

        System.out.println(result);
    }
}

Output:

create Table dbo.provinces(
int int not null identity, 
States nvarchar(100))

public class Main {
	public static void main(String[] args) {
		String sql = "create Table dbo.States(\n" + 
						"int int not null identity, \n" + 
						"States nvarchar(100))";

		String regex = "States(?=\\()";

		String result = sql.replaceAll(regex, "provinces");

		System.out.println(result);
	}
}

create Table dbo.provinces(
int int not null identity, 
States nvarchar(100))

答案2

得分: 1

以下是翻译好的部分：

如我初始评论所述，

在不知道您的现实世界SQL可能有多复杂的情况下，您可以执行

Replace(mytableString, 'States', 'provinces', 'one')

以仅替换第一个出现的 States。

更新：

借鉴Arvind的Java示例答案中的正则表达式...

如果SQL字符串更复杂（例如，在CREATE语句之前有一个ALTER语句等），您可以使用带有正向查找的正则表达式来匹配 States 在开括号 ( 的左侧。

考虑以下示例：

result = reReplace(exampleSQL, 'States(?=\()', 'Provinces', 'all');

您可以在 [TryCF.com 的示例代码][1] 上运行它。

更新 2：

考虑到评论中提供的示例，其中SQL字符串更复杂，表可能包含方括号，您可以执行以下操作，以匹配包含或不包含 [] 以及包含 ( 的 States：

reReplaceNoCase(exampleSQL, '\[?States\]?(?=\()', 'Provinces', 'all')

您可以在 [TryCF.com 的示例代码][2] 上运行它。

更新 3：

为了跟进我的误解，即需要更新 每个表引用 中的 states，这里是一种可能更清洁的方法，可以在两个步骤中完成。

result = replaceNoCase(exampleSQL, '[dbo].[states]', '[dbo].[provinces]', 'all');
// 替换所有作为主键/约束的 states 实例
result = replaceNoCase(result, '_states', '_provinces', 'all') ```

  [1]: https://trycf.com/gist/ca43aa55d1201e2202ecea5f73220dd8/acf2018?theme=monokai
  [2]: https://trycf.com/gist/c4ed802b5339b0fb011cbb2f6696372b/acf2018?theme=monokai

<details>
<summary>英文:</summary>

As stated in my initial comment,

Without knowing if/how complicated your real-world SQL may be, you can do 

    Replace(mytableString, &#39;States&#39;, &#39;provinces&#39;,&#39;one&#39;)

to replace only the first occurrence of `States`.

**UPDATE:**

Borrowing the regex from Arvind&#39;s Java example answer...

If the SQL string is more complex (e.g. an `ALTER` statement ahead of the `CREATE` statement etc.), you can use a regular expression with a positive look ahead to match `States` on the left side of an opening `(`.

Consider this example:

result = reReplace(exampleSQL, 'States(?=()', 'Provinces', 'all');

You can run an [example code at TryCF.com][1]

**UPDATE 2:**

Given the example provided in the comments where the SQL string is more complex and the table may be wrapped in square brackets, you can do the following to match `States` with or without the `[]`s and it including a `(`:

reReplaceNoCase(exampleSQL, '[?States]?(?=()', 'Provinces', 'all')

You can run an [example code at TryCF.com][2]

**UPDATE 3:**

To followup from my misunderstanding that *every table reference* of `states` needs to be updated, here is a possibly cleaner approach over regex that can be accomplished in 2 passes.

// Replace all instances of states as a table declaration
result = replaceNoCase(exampleSQL, '[dbo].[states]', '[dbo].[provinces]', 'all');
// Replace all instances of states as a pk/constraint
result = replaceNoCase(result, '_states', '_provinces', 'all')

  [1]: https://trycf.com/gist/ca43aa55d1201e2202ecea5f73220dd8/acf2018?theme=monokai
  [2]: https://trycf.com/gist/c4ed802b5339b0fb011cbb2f6696372b/acf2018?theme=monokai

</details>