El requisito de refactorización de datos es muy común y vital en las operaciones de minería de datos. En el siguiente artículo: Funciones de cadena SQL para Data Munging (Wrangling), usted aprenderá los consejos para poder comenzar con las funciones de cadena SQL, incluida también la función de subcadena para munging de datos con SQL Server. Todos estamos de acuerdo en que los datos almacenados en una forma a veces requieren una transformación, vamos a ver algunas funciones o tareas comunes para poder cambiar el caso de una cadena, convertir un valor en un tipo diferente, recortar un valor y también poder reemplazar una cadena particular en un campo y así sucesivamente.
Después de leer este artículo, usted comprenderá más sobre:
- Funciones de cadena SQL
- Comprender la función SUBSTRING de SQL Server
- Cómo poder manejar datos usándolos
- Cómo poder usar la función SUBSTRING de SQL Server en la cláusula Where
- Cómo poder ubicar dinámicamente la posición inicial y final de una caracter
- Cómo poder trabajar con una cadena de fecha y hora utilizando la función SUBSTRING de SQL Server
- Cómo poder crear una sub-selección simple usando la función SUBSTRING de T-SQL
- Y más …
Vamos a profundizar en todos los puntos anteriores en este artículo. Nos permitirá poder truncar la longitud del valor de la cadena que es tipo de datos varchar, cuando seleccionamos los datos de la cadena o tablas de entrada.
Se van a necesitar los siguientes tres argumentos.
- El primero es el campo en el que queremos consultar
- El segundo argumento es el carácter inicial,
- El tercer argumento es elcarácter final
La sintaxis de la función SUBSTRING de SQL Server es la siguiente:
SUBSTRING (expresión, posición, longitud)
Parámetros:
- Expresión: cadena de origen de entrada
- Posición: es un valor entero el cual especifica la posición inicial desde la que se pueden extraer los caracteres de la expresión dada. La primera posición de una expresión siempre va a comenzar con 1. La posición inicial puede ser también un número entero negativo.
- Longitud: este es un valor entero positivo. Que especifica el límite final y determina cuántos caracteres se extraerán de la expresión dada
Nota: La función transversal de la subcadena de SQL va siempre de izquierda a derecha.
Ejemplos
En esta sección, vamos a tratar algunos escenarios del mundo real utilizando funciones de cadena SQL. Para algunas demostraciones, se va a utilizar la base de datos Adventureworks2016 y para algunas otras, los datos SQL se generan manualmente. Vamos a ensuciarnos las manos y ver más acción.
- Manejo de datos simples con la función de subcadena de SQL Server
Vamos a comenzar con una consulta SQL básica. En el siguiente ejemplo se devuelve una parte de una cadena de caracteres que comienza en una posición inicial 1 y extrae 5 caracteres de la posición inicial. La función SUBSTRING de T-SQL es bastante útil cuando usted desea asegurarse de que los valores de cadena devueltos por una consulta estarán restringidos a una cierta longitud.
|
1 |
SELECT FirstName, substring(firstname,1,5), lastname FROM Person.Person |
En el siguiente resultado, al utilizar la función SUBSTRING de SQL Server y al poder especificar la columna ‘nombre’, la posición inicial de 1 y la longitud de 5 caracteres, la ejecución de esta consulta SQL va a truncar la longitud de las cadenas en la cual se devuelven de la tabla a cinco caracteres de largo. No importa si el valor en sí en la tabla tiene más de cinco caracteres.
A continuación, el tercer parámetro, la longitud, definida como 15. Esto nos asegurará que no importa la longitud de los datos almacenados en la columna de la tabla, la consulta solo nos va a devolver los primeros 15 caracteres. Esto puede ser muy útil para poder asegurarse de que la salida de los datos de la consulta esté formateada de acuerdo con nuestras expectativas o todo lo que requiere nuestra aplicación.
|
1 |
SELECT FirstName, substring(firstname,1,15), lastname FROM Person.Person |
Ahora, cambie la posición inicial, así también el parámetro de longitud, la longitud, definida como 10.
|
1 |
SELECT FirstName, substring(FirstName,2,5), lastname FROM Person.Person |
Extender la longitud a 10 caracteres nos mostrará valores de cadena más largos. Y así poder cambiar la posición de inicio a 2, por lo cual comenzará a contar caracteres desde 2 a través de la cadena. En este caso, la función de subcadena va a extraer 10 caracteres de la cadena comenzando en la segunda posición. La función SUBSTRING SQL es bastante útil cuando desea asegurarse de que los valores de cadena devueltos por una consulta se restringirán a una determinada longitud.
De esta forma ya tiene una idea de cómo funciona la función SUBSTRING de SQL. En el campo en el cual queremos actuar, comenzamos en qué caracter y terminamos en qué caracter.
- Uso de la función SUBSTRING de SQL Server en la cláusula Where
En el siguiente ejemplo, vamos a usar la columna “nombre”, los dos últimos caracteres se corresponden con la palabra “encendido” usando la función SUBSTRING de SQL en la cláusula Where.
|
1 2 3 4 5 |
SELECT DISTINCT FirstName, lastname FROM Person.Person WHERE SUBSTRING(FirstName, LEN(FirstName)-1,2) = 'on' |
- Localice dinámicamente el carácter inicial y final
En el siguiente ejemplo, la cadena de entrada contiene caracteres alfanuméricos. Mediante la función de la subcadena de SQL Server, el subconjunto numérico de una cadena de la columna col se podrá transformar con CHARINDEX. También puede utilizar la función de cadena SQL PATINDEX para poder encontrar la posición inicial y a su vez el parámetro de longitud de la función SUBSTRING de SQL.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
DROP TABLE IF EXISTS Dummy; CREATE TABLE Dummy (col varchar(20)); INSERT INTO Dummy (col) VALUES ('NY-123 US'), ('AZ-456 GB'), ('MI-789 MO'); select substring (col, charindex('-',col,1)+1, charindex(' ',col,1)-charindex('-',col,1) ) from Dummy; |
O
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
DROP TABLE IF EXISTS Dummy; CREATE TABLE Dummy (col varchar(20)); INSERT INTO Dummy (col) VALUES ('NY-123 US'), ('AZ-456 GB'), ('MI-789 MO'); select substring (col, PATINDEX('%-%',col)+1, PATINDEX('% %',col)- PATINDEX (%-%,col) ) from Dummy; |
En este otro ejemplo, vamos a usar la función SQL PATINDEX, la posición inicial donde se encuentra la cadena ‘-‘. Pero entonces el valor numérico solo comienza en la siguiente posición, por lo que se agrega “1” a la posición inicial. De la misma manera, la longitud se calcula buscando la siguiente posición ‘‘ (espacio) y restando su valor con la posición inicial da la longitud. Entonces ahora, tenemos valores para todos los argumentos. Ejecute la instrucción T-SQL.
- Trabajar con cadenas de fecha y hora
En el siguiente ejemplo, usted podrá ver que la columna col tiene un conjunto de datos y es una cadena de fecha y hora. Al tratar de usar la función SUBSTRING de SQL Server, los valores de entrada se truncan usando la función CHARINDEX o PATINDEX para poder obtener el valor de fecha y hora. Y entonces luego la cadena derivada se convierte a TypeTime para que así pueda usarse para comparar con otros valores de DateTime. En este caso, se va a comparar con la función SQL GETDATE ().
Con esto puede encontrar fácilmente la posición inicial y convertir los datos al tipo de datos requerido (valores válidos) usando las funciones de conversión o emitir funciones. Con CHARINDEX, busque la posición de ‘/’ de la columna de entrada. Después de encontrar la posición, el valor se resta con “3” para poder obtener un valor inicial “12” para la función SUBSTRING de SQL. De la misma manera, la búsqueda se realiza para poder encontrar una posición para el carácter “,” (coma). De esta manera, restando valor con la posición inicial producirá la longitud de la cadena.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
DROP TABLE IF EXISTS Dummy; CREATE TABLE Dummy (col varchar(100)); INSERT INTO Dummy VALUES ('The Date is 04/18/2015 08:00:00, a Saturday'), ('The Date is 02/20/2016 07:00:00, a Sunday'), ('The Date is 03/13/2017 10:00:00, a Monday'), ('The Date is 06/07/2018 09:00:00, a Tuesday') GO select col,charindex('/',col,1)-3,charindex(',',col,1)-charindex('/',col,1) , substring (col, charindex('/',col)-3,charindex(',',col)-charindex('/',col)+3 ) from Dummy |
|
1 2 3 4 5 6 7 8 9 |
select col,patindex('%is%',col)+4,patindex('%,%',col),patindex('%,%',col)-patindex('%is%',col)-5, substring (col, patindex('%is%',col)+4,patindex('%,%',col)-patindex('%is%',col)-4 ) from Dummy where getdate()-300>=cast(substring (col, patindex('%is%',col)+4,patindex('%,%',col)-patindex('%is%',col)-4) as datetime) |
- Crear una sub-selección simple
Una Sub-selección, en SQL Server, es efectivamente una declaración de selección anidada. En SQL, el resultado de una instrucción select es efectivamente una tabla. Por lo general, solo existe en la memoria, pero siempre se puede utilizar, como usaría una tabla. Debido a esto, se puede utilizar una declaración select como una fuente de datos para otra declaración select.
En el ejemplo a continuación, usted puede ver cómo se transforman las columnas con la función SUBSTRING de SQL Server y se utilizan como una tabla para la instrucción de unión SQL.
Si usted ve los valores de la tabla temporal, los primeros dos caracteres de la primera columna son los que representan el estado y los siguientes cuatro caracteres representan el código de estado. De la misma manera, la segunda columna, los dos primeros caracteres representan el país y el resto de los cuatro caracteres forman el código del país. Utilizando la función SUBSTRING de SQL, las dos columnas se analizan y se transforman efectivamente como cuatro columnas nuevas. Estas nuevas columnas se pueden utilizar como si fueran una tabla en una base de datos. En la declaración de selección, se va a unir con la tabla de países para que podamos encontrar el nombre del país.
|
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 |
DROP TABLE IF EXISTS Country; CREATE TABLE Country ( CCode int, CNAME VARCHAR(20)) INSERT INTO Country VALUES ( 1234,'Great Britain'),(5678, 'UNITED STATES'), (4567, 'FRANCE' ) SELECT * FROM Country DROP TABLE IF EXISTS temp; CREATE TABLE temp ( Id1 VARCHAR(6), Id2 VARCHAR(6) ) INSERT INTO temp VALUES ( 'NY1234', 'US5678' ), ( 'AZ5678', 'GB1234' ), ( 'CA9012', 'FR4567' ) SELECT * FROM temp; SELECT SUBSTRING(Id1, 1, 2) AS State, SUBSTRING(Id1, 3,len(ID1)) AS SCode, SUBSTRING(Id2, 1, 2) AS Country, SUBSTRING(Id2, 3,len(id2)) AS CCode FROM temp; SELECT co.CName, ss.CCode FROM Country co INNER JOIN ( SELECT SUBSTRING(Id1, 1, 2) AS State, SUBSTRING(Id1, 3,len(ID1)) AS SCode, SUBSTRING(Id2, 1, 2) AS Country, SUBSTRING (Id2, 3, len(id2)) AS CCode FROM temp ) AS ss ON co.CCode = ss.CCode ; |
Nota: También puede utilizar las funciones de cadena DERECHA e IZQUIERDA. Puede consultar Las funciones de cadena SQL para el artículo Munging de datos (Wrangling) para poder obtener más información.
|
1 |
SELECT left(Id1,2) AS State, right(Id1, 4) AS SCode, left(Id2,2) AS Country,right(Id2, 4) AS CCode FROM temp; |
Resumen
Hasta ahora, hemos visto varios tipos de ejemplos de la función SUBSTRING en SQL Server, las funciones de caracteres que SQL Server se ponen a su disposición para su uso y cómo puede usarlas para poder manipular los valores de cadena en su base de datos y en su conjunto de resultados. De esta manera, es muy útil asegurarse de que la salida de los datos de la consulta SQL esté formateada de acuerdo con las expectativas o los requisitos comerciales.
Igualmente, vamos a necesitar poder comprender la importancia del conjunto de datos. Ya que siempre se recomienda validar completamente el valor de entrada. Existen varias formas de transformar los datos, utilizando la función SUBSTRING T-SQL. En alguno de estos casos, es posible poder transformar usando otras funciones de cadena SQL. En algunos casos, el volumen de datos, el rendimiento y la versión de SQL Server definen las opciones una sobre la otra.
Eso es todo por ahora. Espero que hayan disfrutado este artículo sobre las funciones de cadena de SQL y la función SUBSTRING de SQL Server en particular. No dude en hacer cualquier pregunta en los comentarios a continuación.
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