SQL Server Express：SQL Server Mgmt Studio Express 中的 REPLACE 问题

发布于 2024-08-07 22:08:46 字数 551 浏览 1 评论 0原文

我在 Microsoft SQL Server Management Studio Express 中的 REPLACE SQL 函数上遇到了一个大问题。

当我执行以下查询时

SELECT     REPLACE('ArticleNumber', 'S401', 'I0010')
SELECT     REPLACE('ArticleNumber', 'S302', 'I0020')
SELECT     REPLACE('ArticleNumber', 'S303', 'I0030')    
SELECT     REPLACE('ArticleNumber', 'S304', 'I0040')    
SELECT     REPLACE('ArticleNumber', 'S305', 'I0050')    
SELECT     REPLACE('ArticleNumber', 'S306', 'I0060')    
FROM       tbl.Products

，Studio Express Response 与该 SELECT 无法识别。

我做错了什么？

原文

I have a huge problem with the REPLACE SQL function in Microsoft SQL Server Management Studio Express.

When I do following query

SELECT     REPLACE('ArticleNumber', 'S401', 'I0010')
SELECT     REPLACE('ArticleNumber', 'S302', 'I0020')
SELECT     REPLACE('ArticleNumber', 'S303', 'I0030')    
SELECT     REPLACE('ArticleNumber', 'S304', 'I0040')    
SELECT     REPLACE('ArticleNumber', 'S305', 'I0050')    
SELECT     REPLACE('ArticleNumber', 'S306', 'I0060')    
FROM       tbl.Products

Then Studio Express Response with that SELECT wasn't recognised.

What am I doing wrong?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

千寻… 2024-08-14 22:08:46

根据您想要执行的操作...

您需要嵌套的 REPLACE...

SELECT
     REPLACE
          REPLACE
               REPLACE
                    REPLACE
                         REPLACE
                              REPLACE(ArticleNumber, 'S401', 'I0010'),
                         'S302', 'I0020'),
                    'S303', 'I0030'),
               'S304', 'I0040'),
          'S305', 'I0050'),
     'S306', 'I0060')    
FROM       tbl.Products

..或多个输出列

SELECT
     REPLACE(ArticleNumber, 'S401', 'I0010'),
     REPLACE(ArticleNumber, 'S302', 'I0020'),
     REPLACE(ArticleNumber, 'S303', 'I0030'),   
     REPLACE(ArticleNumber, 'S304', 'I0040'),    
     REPLACE(ArticleNumber, 'S305', 'I0050'),    
     REPLACE(ArticleNumber, 'S306', 'I0060')  
FROM       tbl.Products

注意：错误来自尝试运行此操作的数据库引擎。 SSMS 是一款出色的文本编辑器，具有一些有用的附加功能。

Depending on what you are trying to do...

You need either a nested REPLACE...

SELECT
     REPLACE
          REPLACE
               REPLACE
                    REPLACE
                         REPLACE
                              REPLACE(ArticleNumber, 'S401', 'I0010'),
                         'S302', 'I0020'),
                    'S303', 'I0030'),
               'S304', 'I0040'),
          'S305', 'I0050'),
     'S306', 'I0060')    
FROM       tbl.Products

..or multiple output columns

SELECT
     REPLACE(ArticleNumber, 'S401', 'I0010'),
     REPLACE(ArticleNumber, 'S302', 'I0020'),
     REPLACE(ArticleNumber, 'S303', 'I0030'),   
     REPLACE(ArticleNumber, 'S304', 'I0040'),    
     REPLACE(ArticleNumber, 'S305', 'I0050'),    
     REPLACE(ArticleNumber, 'S306', 'I0060')  
FROM       tbl.Products

Note: the error comes from the database engine that tries to run this. SSMS is a glorified text editor with some useful extras..

回复收藏 0 原文

夏见 2024-08-14 22:08:46

文章编号应该用引号引起来吗？
您的替换不会替换任何内容，因为 S401、S302 等绝对不在“ArticleNumber”中。

您需要遵循 gbn 使用嵌套语句的建议，但使用实际的列。

SELECT
 REPLACE(
      REPLACE(
           REPLACE(
                REPLACE(
                     REPLACE(
                          REPLACE(ArticleNumber, 'S401', 'I0010'),
                     'S302', 'I0020'),
                'S303', 'I0030'),
           'S304', 'I0040'),
      'S305', 'I0050'),
 'S306', 'I0060')
FROM tbl.Products

克里斯

Should articlenumber be in quotes?
Your replace is not going to replace anything as S401, S302, etc... are definitely not in 'ArticleNumber.'

You'll want to follow gbn's advice of using a nested statement, but with the actual column.

SELECT
 REPLACE(
      REPLACE(
           REPLACE(
                REPLACE(
                     REPLACE(
                          REPLACE(ArticleNumber, 'S401', 'I0010'),
                     'S302', 'I0020'),
                'S303', 'I0030'),
           'S304', 'I0040'),
      'S305', 'I0050'),
 'S306', 'I0060')
FROM tbl.Products

Kris

回复收藏 0 原文

寒江雪… 2024-08-14 22:08:46

也许我只是不明白你想要做什么，但从我在这里可以读到的内容来看，你试图用“I0010”替换字符串“ArticleNumber”中的“S401”，所以选择结果将始终是“ArticleNumber”.... 那么

FROM tbl.Products 是无用的，那么您应该尝试不带引号的 ArticleNumber ，请参阅 GBN 答案以获取确切的语法

如果您想替换表 produtcts 中的 ArticleNumber 字段的值，

回复收藏 0 原文

喜爱皱眉﹌ 2024-08-14 22:08:46

这是一种略有不同的方法。

SELECT COALESCE(ch.New, pr.ArticleNumber) AS ArticleNumber
FROM tbl.Products pr
   LEFT JOIN (
   SELECT 'S401' AS Old, 'I0010' as New
   UNION
   SELECT 'S302' AS Old, 'I0020' AS New
   UNION ... etc
) ch ON ch.Old = pr.ArticleNumber

This is a slightly different way of doing it.

SELECT COALESCE(ch.New, pr.ArticleNumber) AS ArticleNumber
FROM tbl.Products pr
   LEFT JOIN (
   SELECT 'S401' AS Old, 'I0010' as New
   UNION
   SELECT 'S302' AS Old, 'I0020' AS New
   UNION ... etc
) ch ON ch.Old = pr.ArticleNumber

回复收藏 0 原文

我不在是我 2024-08-14 22:08:46

删除 ArticleNumber 周围的引号并嵌套 REPLACE：

SELECT  
  ArticleNumber = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ArticleNumber 
    , 'S401', 'I0010')    
    , 'S302', 'I0020')    
    , 'S303', 'I0030')    
    , 'S304', 'I0040')    
    , 'S305', 'I0050')    
    , 'S306', 'I0060')    

FROM tbl.Products

Remove the quotes around ArticleNumber and nest the REPLACEs:

SELECT  
  ArticleNumber = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ArticleNumber 
    , 'S401', 'I0010')    
    , 'S302', 'I0020')    
    , 'S303', 'I0030')    
    , 'S304', 'I0040')    
    , 'S305', 'I0050')    
    , 'S306', 'I0060')    

FROM tbl.Products

回复收藏 0 原文

~没有更多了~