CODEWITHSUNDEEP
sqlpervasive
seb
seb

Reputation: 161

SQL replace function

I would like to replace characters within my WHERE statement that have the first character of S,

right now all i have is the following:

WHERE i1.CODE = REPLACE(i2.CODE, 'S', 'U')

but this would replace all S's with U's. I just wish to replace the S with a U only when the S is the first character

thank you!

Upvotes: 2

Views: 3327

Answers (3)

Thomas
Thomas

Reputation: 64645

Where i1.Code = Case
                    When Substring( i2.Code, 1, 1) = 'S' 
                        Then 'U' + Substring( i2.Code, 2, Len( i2.Code ) )
                    Else i2.Code
                    End

Another alternative:

Where i1.Code = Case
                    When i2.Code Like 'S%'
                        Then 'U' + Substring( i2.Code, 2, Len( i2.Code ) )
                    Else i2.Code
                    End

As ErikE suggested, you can also replace Len(i2.Code), with an arbitrarily large static value which might make a small speed improvement.

Upvotes: 2

therealmitchconnors
therealmitchconnors

Reputation: 2760

DISCLAIMER: This is really, really ugly, but I think it works.

REPLACE(LEFT(i2.CODE, 1), 'S', 'U') + RIGHT(i2.CODE, LEN(i2.CODE)-1)

Upvotes: 1

Joe Stefanelli
Joe Stefanelli

Reputation: 135808

WHERE i1.CODE = REPLACE(LEFT(i2.CODE,1), 'S', 'U') + RIGHT(i2.CODE, LEN(i2.CODE)-1)

Upvotes: 4

Related Questions