SQL replace function

Question

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!

score 4 · Accepted Answer · answered Jun 22 '11 at 18:47

4

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

answered Jun 22 '11 at 18:47

Joe Stefanelli

132,803
19
237
235

I always like using `8000` or `2147483647` instead of `Len` in these situations. It should theoretically use slightly less CPU and it makes for a shorter expression. – ErikE Jun 22 '11 at 18:54
ok thank you , it seems to be running but it is quite slow, but that is ok as this statement will only be done once. Just for testing is there a way to limit an UPDATE statement so that it only runs for a certain amount of row updates so that I can test if it works instead of trying to run it for my entire table (which is 100k + rows) and then find out the result isnt what I wanted and waste a lot of time? thanks – seb Jun 22 '11 at 19:08
@seb: `TOP (n)`, as in SQL Server, or `LIMIT n`, as in MySQL/PostgreSQL, or whatever other equivalent Pervasive supports. – Andriy M Jun 22 '11 at 19:21
@seb If you're not sure it does what you want, please do it as a `SELECT` statement first before you `UPDATE`, with a "currentvalue" column and a "newvalue" column so you can compare. – ErikE Jun 22 '11 at 20:13

Thomas · Answer 2 · 2011-06-22T19:15:16.583

2

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.

edited Jun 22 '11 at 19:15

answered Jun 22 '11 at 18:47

Thomas

63,911
12
95
141

Do you think the first substring is better than `LIKE 'S%'`? – ErikE Jun 22 '11 at 18:55
@ErikE - Good question. It may not matter. It would depend on whether the query engine could intelligently parse the Case statement. However, I'll post as an alternative. – Thomas Jun 22 '11 at 19:14

score 1 · Answer 3 · answered Jun 22 '11 at 18:51

1

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)

answered Jun 22 '11 at 18:51

therealmitchconnors

2,732
1
18
36

I always like using `8000` or `2147483647` instead of `Len` in these situations. It should theoretically use slightly less CPU and it makes for a shorter expression. – ErikE Jun 22 '11 at 18:54
sorry, I am not following... is 8000 the name of a sql string function? – therealmitchconnors Jun 22 '11 at 19:07
@therealmitchconnors: No, 8000 was meant as a second parameter of `RIGHT`, instead of `LEN`. – Andriy M Jun 22 '11 at 19:19
Well, in your case, yes, the length of the substring to return should be calculated precisely. If you used SUBSTRING, though, you could put 8000 as the length (as in `SUBSTRING(CODE, 2, 8000)`). – Andriy M Jun 22 '11 at 19:28
Oops, sorry @therealmitch and @Andriy, I didn't notice it was a `Right()` function... yes, I meant with `Substring`. – ErikE Jun 22 '11 at 20:10

SQL replace function

3 Answers3