SQL Server Express: REPLACE problem in SQL Server Mgmt Studio Express

Question

Hello All

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?

Answer 1

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..

Answer 2

maybe i jsut dont get what you are trying to do but from what i can read here you are trying to replace 'S401' in the string 'ArticleNumber' by 'I0010' so the select result will always be 'ArticleNumber' .... and the

FROM tbl.Products is useless

if u want to replace the value of the field ArticleNumber from table produtcts then you should try ArticleNumber with no quote jsut refer to GBN answer for the exact syntax

Answer 3

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

Answer 4

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

Answer 5

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