Question 19159 in Amazon Web Sevices Question posted in
The official Amazon Web Services documentation can be found here.

Substitute for Function STUFF (SQL Server) in AWS redshift – Amazon Web Sevices

I have to replace first 3 digits of a column to a fix first 3 digits (123)
Working SQL Server code. (Not working on AWS RedShift)

Code:

Select 
Stuff (ColName,1,3,'123')as NewColName
From DataBase.dbo.TableName

eg 1 -Input — 8010001802000000000209092396—output -1230001802000000000209092396
eg 2 -Input — 555209092396- –output -123209092396

it should replace the first 3 digits to 123 irrespective of its length.

Please advice anything that is supported in AWS Redshift.

yet trying using substring and repalce.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Got this and it worked

    --Using Substring and concat
Select 
cast('123'+substring(ColName,4,LEN(ColName)) as numeric (28)) as NewColName 
From DataBase.dbo.TableName

  2. 0

    I see that AWS RedShift was based on an old version of Postgres, and I looked up the SUBSTRING function for you (https://docs.aws.amazon.com/redshift/latest/dg/r_SUBSTRING.html), which is pretty forgiving of its argument values.

    In this sample in Transact-SQL, and as documented for RedShift, the third argument of SUBSTRING can be much longer than the actual strings without causing an error. In Transact-SQL, even the second argument is "forgiving" if it starts after the end of the actual string:

    ;
WITH RawData AS
     (SELECT * FROM (VALUES ('8010001802000000000209092396'),
                            ('555209092396'),
                            ('AB')
                    ) AS X(InputString)
     )
     SELECT InputString, '123' + SUBSTRING(InputString, 4, 1000) AS OutputString
            FROM RawData

InputString                     OutputString
8010001802000000000209092396    1230001802000000000209092396
555209092396                    123209092396
AB                              123

    As it appears that the concatenation operator in Redshift is ||, I think your expression will be very close to:

    '123' || SUBSTRING(InputString, 4, 1000)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search