Substring vs Replace vs Stuff en SQL Server
Ejemplo
A partir de nuestro nombre de dominio, hemos proporcionado un ejemplo de eliminación de "https://" de tres maneras.
Hemos proporcionado un SQL de prueba como ejemplo y exploraremos las siguientes funciones incorporadas:
- SUBSTRING
- REPLACE
- STUFF
- LEFT/RIGHT
- CHARINDEX
- REVERSE
- ISNULL/NULLIF
SQL
DECLARE @e NVARCHAR(MAX)='https://www.claytabase.co.uk'SELECT SUBSTRING(@e,9,100)SELECT REPLACE(@e,'https://','')SELECT STUFF(@e,1,8,'')--www.claytabase.co.uk--www.claytabase.co.uk--www.claytabase.co.ukSUBSTRING
Usa SUBSTRING para mostrar parte de una cadena. En este ejemplo queremos que la posición inicial sea 9. Utiliza longitudes mayores cuando quieras mostrar el resto del final de la cadena.
SUBSTRING(expression, start, length)
LEFT & RIGHT
REPLACE
Utiliza REPLACE para reemplazar todas las apariciones de una cadena dentro de otra. Si sólo quieres reemplazar una ocurrencia, utiliza STUFF.
REPLACE(string_expression, string_pattern, string_replacement)
STUFF
Utiliza STUFF para sustituir parte de una cadena de texto por otra en la que conozcas las posiciones de los caracteres que deseas reemplazar.
STUFF(character_expression, start, length, replace_expression)
Utiliza la función CHARINDEX para encontrar la posición
Es muy poco probable que conozcas la posición de la cadena que quieres reemplazar todo el tiempo, así que normalmente usamos la función CHARINDEX para encontrar la posición.
A continuación la hemos utilizado para separar la misma cadena con SUBSTRING y RIGHT, pero las opciones son prácticamente ilimitadas en cuanto a las combinaciones que se pueden utilizar.
Cuando utilices CHARINDEX como posición inicial necesitarás sumar o restar 1 al valor para obtener el elemento de visualización correcto.
SQL
DECLARE @e NVARCHAR(MAX)='https://www.claytabase.co.uk'SELECT CHARINDEX('.',@e,1),SUBSTRING(@e,CHARINDEX('.',@e,1)+1,100)SELECT CHARINDEX('.',@e,CHARINDEX('.',@e,1)+1),SUBSTRING(@e,CHARINDEX('.',@e,CHARINDEX('.',@e,1)+1)+1,100)SELECT CHARINDEX('.',REVERSE(@e),1),RIGHT(@e,CHARINDEX('.',REVERSE(@e),1)-1)--12,claytabase.co.uk--23,co.uk--3,ukCómo evitar errores
El uso de la suma o resta de 1 junto con las otras funciones puede resultar en un valor negativo que se analiza, lo que causará un error. En el ejemplo hemos resuelto este problema estableciendo -1 a NULL, que se convertiría implícitamente a 0.
Workaround
DECLARE @e NVARCHAR(MAX)='https://www.claytabase.co.uk'SELECT CHARINDEX('x',REVERSE(@e),1),NULLIF(RIGHT(@e,CHARINDEX('x',REVERSE(@e),1)),-1)Combinación de varias funciones de cadena
Mediante las distintas funciones anteriores, hemos extraído las distintas partes de una cadena URL.
Éstas extraerán el protocolo, el dominio, la página y las cadenas de consulta si existen.
Complex SQL
DECLARE @e NVARCHAR(MAX)='https://www.claytabase.co.uk/Business-Solutions/Database-Consultancy-Services/Technical-Articles/SQL-Server-Data/Substring-vs-Replace-vs-Stuff?qrystr=claytabase'SELECT CHARINDEX('//',@e,1),LEFT(@e,CHARINDEX('//',@e,1)+1)--Protocol add 1 to pull through complete valueSELECT CHARINDEX('//',@e,1),CHARINDEX('/',@e,CHARINDEX('//',@e,1)+2),SUBSTRING(@e,CHARINDEX('//',@e,1)+2,CHARINDEX('/',@e,10)-LEN(LEFT(@e,CHARINDEX('//',@e,1)+2)))--CHARINDEX needs adjusting to remove protocol lengthSELECT CHARINDEX('/',@e,CHARINDEX('//',@e,1)+2),ISNULL(NULLIF(CHARINDEX('?',@e,1)-1,-1),LEN(@e)),SUBSTRING(@e,CHARINDEX('/',@e,CHARINDEX('//',@e,1)+2)+1,ISNULL(NULLIF(CHARINDEX('?',@e,1)-1,-1),LEN(@e))-CHARINDEX('/',@e,CHARINDEX('//',@e,1)+2))SELECT CHARINDEX('?',REVERSE(@e),1),RIGHT(@e,ISNULL(NULLIF(CHARINDEX('?',REVERSE(@e),1)-1,-1),0))--7,https://--7,29,www.claytabase.co.uk--29,123,Database-Consultancy-Services/Technical-Articles/SQL-Server-Data/Substring-vs-Replace-vs-Stuff--18,qrystr=claytabaseSET @e='http://www.claytabase.co.uk/'SELECT CHARINDEX('//',@e,1),LEFT(@e,CHARINDEX('//',@e,1)+1)--Protocol add 1 to pull through complete valueSELECT CHARINDEX('//',@e,1),CHARINDEX('/',@e,CHARINDEX('//',@e,1)+2),SUBSTRING(@e,CHARINDEX('//',@e,1)+2,CHARINDEX('/',@e,10)-LEN(LEFT(@e,CHARINDEX('//',@e,1)+2)))--Domain needs adjusting to remove protocolSELECT CHARINDEX('/',@e,CHARINDEX('//',@e,1)+2),ISNULL(NULLIF(CHARINDEX('?',@e,1)-1,-1),LEN(@e)),SUBSTRING(@e,CHARINDEX('/',@e,CHARINDEX('//',@e,1)+2)+1,ISNULL(NULLIF(CHARINDEX('?',@e,1)-1,-1),LEN(@e))-CHARINDEX('/',@e,CHARINDEX('//',@e,1)+2))SELECT CHARINDEX('?',REVERSE(@e),1),RIGHT(@e,ISNULL(NULLIF(CHARINDEX('?',REVERSE(@e),1)-1,-1),0))--6,http://--6,28,www.claytabase.co.uk--28,28,--0,