6

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

  1. Q234567888
  2. R264747848
  3. B264712481

output

  1. I234567888
  2. I264747848
  3. 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 Mar 21 '18 at 10:44
6

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;
|improve this answer|||||
  • @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 Mar 22 '18 at 16:08
2
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.

|improve this answer|||||
2

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.

|improve this answer|||||
1

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.

|improve this answer|||||
0

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.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.