En este artículo, le voy a mostrar cómo poder buscar y reemplazar datos dentro de cadenas. Vamos a demostrar cómo poder usar la función SQL REPLACE, donde puedes buscar una subcadena dentro de una cadena y luego poder reemplazarlas.
Este artículo se va a responder las preguntas más frecuentes:
- ¿Qué hace la función de reemplazo de SQL en SQL Server?
- ¿Cómo poder utilizar la función COLLATE con REPLACE en SQL Server?
- ¿Cómo se puede reemplazar múltiples caracteres en SQL?
- ¿Cómo tratar de realizar una actualización usando REPLACE en SQL Server?
- ¿Cómo poder preparar el código T-SQL para realizar un REPLACE?
También algunas otras funciones de cadena se analizaran en los siguientes artículos Descripción general de la función de subcadena de SQL y funciones de cadena de SQL para Data Munging (Wrangling).
Sintaxis
REPLACE (Expresión, patrón, reemplazo)
Expresión: el valor de cadena de entrada en el que tiene que realizar la función de reemplazo.
Patrón: la subcadena para poder evaluar y proporcionar una posición de referencia para el campo de reemplazo.
REEMPLAZO: REEMPLAZA la cadena especificada o el valor de carácter de la expresión dada.
Nota: La función SQL REPLACE es la que realiza comparaciones basadas en la clasificación de la expresión de entrada.
Ejemplos
Cómo podemos realizar un simple REPLACE
El siguiente SQL usa la palabra clave REPLACE (reemplazo) para encontrar una cadena de patrón coincidente y reemplazarla por otra cadena.
|
1 |
SELECT REPLACE('SQL Server vNext','vNext','2017') SQL2017; |
A continuación, está el conjunto de resultados.
|
1 |
GO |
Uso de la función COLLATE con REPLACE (Reemplazo)
A continuación, se va a utilizar en SQL la función de intercalación entre mayúsculas y minúsculas para poder validar la expresión dentro de la función REPLACE de SQL.
|
1 |
SELECT REPLACE('SQL Server vNext' COLLATE Latin1_General_CS_AS,'vnext','2017') SQL2017; |
La salida es una entrada directa de la expresión, ya que no se puede validar el patrón de entrada.
En el siguiente SQL se va a usar el mismo ejemplo, pero ahora la función de intercalación entre mayúsculas y minúsculas se va a usar para poder validar la expresión dentro de la función:
|
1 |
SELECT REPLACE('SQL Server vNext' COLLATE Latin1_General_CI_AS,'vnext','2017') SQL2017; |
La salida nos muestra que los valores coinciden independientemente de los casos:
Cómo poder reemplazar múltiples patrones en una cadena ya dada
En el próximo ejemplo se va a utilizar la función de reemplazo de SQL para poder reemplazar múltiples patrones de la expresión 3 * [4 + 5] / {6-8}.
|
1 |
SELECT REPLACE(REPLACE(REPLACE(REPLACE('3*[4+5]/{6-8}', '[', '('), ']', ')'), '{', '('), '}', ')'); |
Podemos apreciar que la función REPLACE está anidada y se la trata de usar varias veces para reemplazar la cadena correspondiente según los valores posicionales ya definidos dentro de la función REPLACE de SQL.
Como en el ejemplo mencionado anteriormente, tratamos de usar TRANSLATE, una nueva función de SQL Server 2017. Esta es una buena función de cadena de reemplazo para la función SQL REPLACE.
- Puede consultar en el siguiente artículo Funciones principales de cadenas SQL en SQL Server 2017 para poder obtener más información.
En la siguiente consulta se va a reemplazar el patrón A, C y D con los valores 5, 9, 4 y va a generar una nueva columna llamada GRPCODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
DROP TABLE IF EXISTS #temp; CREATE TABLE #temp (name NVARCHAR(50), GRP NVARCHAR(100) ); INSERT INTO #temp VALUES ('Prashanth', 'AB' ), ('Kiki', 'ABC' ), ('Steven', 'ABCD' ); |
La siguiente función SQL REPLACE se somete a una ejecución de 3 iteraciones para poder obtener el resultado deseado. En el primero, se evalúa el patrón de entrada “A” y si se encuentra, se reemplazan 5. El segundo, B es evaluado. Si se encuentra, el valor numérico 9 se reemplaza. Finalmente, D es reemplazado por 4.
|
1 2 3 4 |
SELECT Name, GRP, REPLACE (REPLACE (REPLACE(GRP, 'A', '5'), 'C', 9), 'D', 4) GRPCODE FROM #temp; |
Aquí hay otro ejemplo para poder actualizar usando la función SQL REPLACE. En este caso, con una columna GRP con el CÓDIGO GRP, ejecute el siguiente SQL.
|
1 2 3 |
UPDATE #temp SET GRP = replace(replace(REPLACE(GRP, 'A', '5'), 'C', 9), 'D', 4); |
Ahora, vamos a echar un vistazo a los datos
|
1 2 |
SELECT * FROM #temp; |
Casos de uso
Por lo general, cuando se migra una base de datos, los objetos dependientes también deben migrarse. Como por ejemplo, en un servidor vinculado o en valores de columna específicos de tablas específicas que hacen referencia a una tabla renombrada. Vamos a tratar de profundizar sobre el proceso de como poder manejar dichos cambios dinámicamente usando la función SQL REPLACE con construcciones T-SQL.
En un proyecto complejo de migración de base de datos, el servidor va a tener múltiples configuraciones de servidor vinculado. Estos servidores vinculados ya fueron referidos y mencionados en múltiples procedimientos almacenados. Es necesario poder encontrar y reemplazar los scripts de procedimientos almacenados, pero la intención es automatizar todo el proceso para asegurarse de que no haya que hacer ninguna actualización manual.
Vamos a ver estos simples pasos.
Paso 1:
En este caso, el patrón de búsqueda es empleado. Aparte de eso, verá que el carácter de escape personalizado se utiliza para poder hacer escapar caracteres especiales “[” y “] en el patrón de búsqueda.
|
1 2 3 4 5 6 |
DECLARE @searchPattern VARCHAR(100)= 'employee'; SELECT DISTINCT 'sp_helptext '''+OBJECT_SCHEMA_NAME(id)+'.'+OBJECT_NAME(id)+''' ' FROM syscomments WHERE TEXT LIKE '%'+REPLACE(REPLACE(@searchPattern, ']', '\]'), '[', '\[')+'%' ESCAPE '\' ORDER BY 'sp_helptext '''+OBJECT_SCHEMA_NAME(id)+'.'+OBJECT_NAME(id)+''' '; |
Podemos ver en el resultado que se van a enumerar 13 objetos satisfaciendo la condición de búsqueda empleado.
Paso 2:
Ahora, es sencillo recorrer los objetos enumerados y generar el script y poder almacenarlo en una tabla temporal.
Paso 3:
Hay que realizar una actualización simple para modificar la palabra clave crear a “ALTER”(Modificar). De esta forma, el script está ya listo para ejecutarse en la base de datos de destino. En algunos casos, solo se va a necesitar conservar el script. Eso está bien, pero no ejecutes este paso.
|
1 2 3 4 5 |
UPDATE @sptemp SET storedProcedureText = REPLACE(REPLACE(storedProcedureText, 'CREATE PROCEDURE', 'ALTER PROCEDURE'), @searchpattern, @replacestring); SELECT storedProcedureText FROM @sptemp; |
Ahora, hay que copiar y pegar el resultado en una nueva ventana de consulta y luego asegurarse de que todo se vea bien para poder ejecutar el SQL.
En el resultado que tenemos a continuación, se puede ver que el patrón de búsqueda empleado es reemplazado por Prashanth.
Ahora puede consultar el código completo en la sección Apéndice.
Apéndice
El código T-SQL sirve para reemplazar una cadena en todos los procedimientos almacenados previstos automáticamente.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
SET NOCOUNT ON; DECLARE @sql NVARCHAR(MAX), @searchpattern VARCHAR(100)= 'employee', @replacestring VARCHAR(100)= 'Prashanth'; -- this will hold stored procedures text DECLARE @sptemp TABLE(storedProcedureText VARCHAR(MAX)); DECLARE cur CURSOR FAST_FORWARD FOR SELECT DISTINCT 'sp_helptext '''+OBJECT_SCHEMA_NAME(id)+'.'+OBJECT_NAME(id)+''' ' FROM syscomments WHERE TEXT LIKE '%'+REPLACE(REPLACE(@searchpattern, ']', '\]'), '[', '\[')+'%' ESCAPE '\' ORDER BY 'sp_helptext '''+OBJECT_SCHEMA_NAME(id)+'.'+OBJECT_NAME(id)+''' '; --Open Cursor OPEN cur; FETCH NEXT FROM cur INTO @sql; WHILE @@FETCH_STATUS = 0 BEGIN --stored procedure script into a column INSERT INTO @sptemp EXEC (@sql); -- Add GO keyword INSERT INTO @sptemp VALUES('GO'); FETCH NEXT FROM cur INTO @sql; END; CLOSE cur; DEALLOCATE cur; UPDATE @sptemp SET storedProcedureText = REPLACE(REPLACE(storedProcedureText, 'CREATE PROCEDURE', 'ALTER PROCEDURE'), @searchpattern, @replacestring); SELECT storedProcedureText FROM @sptemp; |
Mi especialidad es el diseño y la implementación de soluciones de alta disponibilidad y la migración de bases de datos multiplataforma. Las tecnologías en las que trabajo actualmente son SQL Server, PowerShell, Oracle y MongoDB.
Ver todas las publicaciones de Prashanth Jayaram
- Descripción general de la función SQL CAST y SQL CONVERT - December 6, 2019
- Revisión del operador relacional y descripción general dePivot y Unpivot estático y dinámico de SQL - November 6, 2019
- Revisión, ejemplos y uso de SQL Union - November 4, 2019