Replace Character Without Using Looped REPLACE Function

Question

My SQL Server database input values follow a pattern of where the first position is a character and then followed by number. I want replace the first character with another character or characters . For example:

input

Q234567888
R264747848
B264712481

output

I234567888
I264747848
U264712481

Potential values for the first position are [A-Z].

I am looking for an option without using multiple REPLACE statement and not using CASE.

If I use CASE, I will have to check 26 cases or if I use REPLACE in a nested way, I may end up using 26 REPLACE statements. I am trying to avoid these.

Please suggest any better way to do achieve the results.

What version of SQL Server are you using? (2016, 2017)?

Scott Hodgin - Retired
– Scott Hodgin - Retired

2018-03-21 10:44:24 +00:00
Commented Mar 21, 2018 at 10:44 — Scott Hodgin - Retired
– Scott Hodgin - Retired, Commented Mar 21, 2018 at 10:44

Mikael Eriksson · Accepted Answer · 2018-03-21 07:32:49Z

7

Create a mapping between the old value and the new value in a table value constructor and use stuff to replace the character.

declare @T table(Value varchar(10));

insert into @T values
('Q234567888'),
('R264747848'),
('B264712481');

select stuff(T.Value, 1, 1, (
                            select V.NewValue
                            from (values('Q', 'I'),
                                        ('R', 'I'),
                                        ('B', 'U')) as V(OldValue, NewValue)
                            where V.OldValue = left(T.Value, 1)
                            ))
from @T as T;

answered Mar 21, 2018 at 7:32

Mikael Eriksson

22.3k5 gold badges63 silver badges106 bronze badges

@sham: In case you are thinking of updating those millions of rows in one go, it won't matter which replacement method you will eventually choose, be it a CASE expression, nested REPLACE calls, Mikael's mapping table or anything else.

Andriy M
– Andriy M

2018-03-22 16:08:57 +00:00
Commented Mar 22, 2018 at 16:08

Add a comment |

Solomon Rutzky · Accepted Answer · 2018-03-22 16:48:09Z

Just to have this stated as an option since the O.P.'s version of SQL Server has not been mentioned, and the following is a valid approach starting with SQL Server 2017:

The TRANSLATE function is well suited for a direct 1-to-1 replacement of single characters:

DECLARE @TEST TABLE([Stuff] VARCHAR(10));

INSERT INTO @TEST ([Stuff]) VALUES
('Q234567888'),
('R264747848'),
('B264712481');

UPDATE tst
SET    tst.[Stuff] = 
          STUFF(tst.[Stuff], 1, 1, (
              TRANSLATE(LEFT(tst.[Stuff], 1), 'QRB', 'IIU')
              ))
FROM   @TEST tst;

SELECT * FROM @TEST;

returns:

Stuff
----------
I234567888
I264747848
U264712481

P.S. Ignore SSMS (at least through v 17.5, I haven't yet tried 17.6) flagging TRANSLATE as an invalid function. This behavior has been reported here.

BWFC · Accepted Answer · 2018-03-21 12:45:20Z

DECLARE @values TABLE
 (  
     String CHAR(10)
 )

INSERT INTO @values
values
('Q234567888'),
('R264747848'),
('B264712481');

SELECT 
    REPLACE(String,LEFT(String,1),ca.NewValue)
FROM @values
CROSS APPLY 
(SELECT * FROM (values('Q', 'I'),
('R', 'I'),
('B', 'U'))as V(OldValue, NewValue)
WHERE v.OldValue = LEFT(string,1)                               
)ca

Here's another approach. While testing this, I ran @Mikael Eriksson's method and the CASE\REPLACE with 26 clauses, over a 3 million row test table and the CASE method was quickest hands down. That said, none were slow and the above and @Mikael's shook out evenly. Of course, YMMV.

Andriy M · Accepted Answer · 2018-03-22 16:07:54Z

I understand your concern with sprawling queries, especially if they are a) slow at run-time or b) unmaintainable. Twenty six nested REPLACEs would definitely qualify for the latter. I'm not convinced that CASE need share this fate.

You don't fully explain in the question the mapping rules from input prefix to output prefix. If there are sequential runs in the input, CASE can be quite compact and legible, and hence maintainable.

Both the form

case
    when LEFT(c, 1) between 'A' and 'P' then 'U'
    when LEFT(c, 1) between 'Q' and 'R' then 'I'
    ...
end

and

case 
    when LEFT(c, 1) in ('Q', 'R') then 'I'
    when LEFT(c, 1) in ('B')      then 'U'
    ...
end

are valid, compact, legible and obvious. Performance-wise, it is likely to be good since the values will be^† hard-coded in the query plan, there will be no additional disk access, or even buffer pool references.

_{^†Of course the query optimizer is free to treat these in any way it wants. If embedded in a sufficiently labyrinthine query, this may not hold. Only you know the full complexity of the actual query.}

KumarHarsh · Accepted Answer · 2018-03-23 11:35:48Z

Most importantly there is no logic behind 'Q' for 'I' or 'R' for 'I' etc,if there is any logic then let us know.

you can keep one temp table to make the sript more cleaner.

create table #tmp(oldval char(1),newval char(1))
insert into #tmp VALUES('Q','I'),('R','I'),('B','U')

declare @T table(Value varchar(10));

insert into @T values
('Q234567888'),
('R264747848'),
('B264712481');

update t set Value=newval+stuff(T.Value, 1, 1,'')
from @T T 
inner join #tmp tmp on left(t.Value,1)=tmp.oldval

select * from @T

drop table #tmp

if there is million of record then you need paging .

I don't think what you have explain is real scenario.

Stack Exchange Network

Replace Character Without Using Looped REPLACE Function

input

output

5 Answers 5

Your Answer

Hot Network Questions

Replace Character Without Using Looped REPLACE Function

input

output

5 Answers 5

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions