SQL Server – Replace Character Without Looped REPLACE Function

sql server

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.

Best Answer

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;

Related Solutions

Sql-server – Are there ways to only replace SQL Server stored procedures when the definition has changed

We recently had this discussion at my workplace.

First, I want to commend you for doing the "right thing" by using HASHBYTES() over CHECKSUM() to detect changes. Microsoft specifically cautions against using CHECKSUM(@input) for this purpose as its collision rate is very high compared to that of HASHBYTES('SHA1', @input). One advantage CHECKSUM() does have, though, is that there is no (obvious) restriction on the size of its input.

Second, if you use HASHBYTES() I recommend using SHA1 as your hash algorithm. Of the available options SHA1 has the lowest collision rate, and speed is not a concern for your use case.

Finally, To use HASHBYTES() against inputs larger than 8000 bytes you'll have to:

Split your input into 8000 byte chunks.
Hash each chunk.
Somehow combine the resulting hashes and hash them to get your final output.

You can do this in one of two ways:
1. Convert your hash outputs into strings, concatenate them, and hash the result.
2. Stick all your hash outputs into a memory table and take their aggregate checksum using CHECKSUM_AGG().
Encapsulate this work as a function that takes NVARCHAR(MAX) as its input.

All that said, it is all-around simpler to just compare the proc definitions directly using OBJECT_DEFINITION() as gbn suggested, or to simply push all definitions out everywhere as often as you like, as Mike suggested.

I wonder what kind of environment would significantly benefit from a process that deployed only changed procedures and used hashes to avoid copying around and comparing full definitions. You'd need to have a lot of procedures to keep in sync.

Sql-server – How to avoid duplicating information in a series of nested SELECT statements

;WITH x AS 
(
  SELECT g.guestid, e.[year], e.run, c = COUNT(*)
  FROM dbo.guest AS g
  INNER JOIN dbo.[order] AS o
  ON g.guestid = o.guestid
  INNER JOIN dbo.eventseat AS es
  ON o.orderid = es.orderid
  INNER JOIN dbo.event2 AS e
  ON e.eventid = es.eventid
  WHERE e.incometype = 'T'
  AND e.eventtype in ('SS', 'BMF')
  AND es.status in ('2','4')
  AND es.price NOT IN ( '0.00', '5.00')
  AND e.[year] = '2010'
  AND e.run IN ('SS-DANCE', 'SS-FILM', 'SS-OPERA')
  GROUP BY g.guestid, e.[Year], e.run
),
y AS 
(
  SELECT * FROM x PIVOT (MAX(c) FOR run IN 
  ([SS-DANCE],[SS-FILM],[SS-OPERA])) AS z
)
SELECT y.guestid, y.[year], g.[first], g.[last], 
  [SS-DANCE] = COALESCE(y.[SS-DANCE], 0), 
  [SS-FILM]  = COALESCE(y.[SS-FILM], 0), 
  [SS-OPERA] = COALESCE(y.[SS-OPERA], 0)
FROM y 
INNER JOIN dbo.guest AS g
ON y.guestid = g.guestid;

I'm not sure if my where clause is going to work for you as written. This currently assumes you only care about these three types (dance, film and opera) and you only care about the year 2010. If you need to cross years you can remove that WHERE clause. If you only care about dance in 2010 and film in any year you'll need to write a more complex set of where clauses.

Some comments:

try to avoid reserved words like order, year, first and last. These only complicate matters because they need to be enclosed in square brackets (which are preferable over "double quotes" mostly for readability and unambiguity).
always use dbo. (or whatever schema prefix is appropriate).
as I suggested above, stick to explicit INNER JOIN syntax. FROM x, y, z is for the birds.