Cadenas

Funciones de cadena

Tamaño de cadena

LEN(column)
Da el tamaño de caracteres en número de la cadena. Funciona tb si la columna no es de texto.

Repetir un carácter

REPLICATE( '0', 4 )
---
'0000'

Concatenar

con el signo '+'
Si es un número hay que pasarlo a texto con conversión de variables

'D' + CAST( 3 AS VARCHAR )
---
'D3'

CASE WHEN PROVINCIAS.ID_PROVINCIAS < 10 THEN '0' + CAST(PROVINCIAS.ID_PROVINCIAS AS VARCHAR) ELSE PROVINCIAS.ID_PROVINCIAS END AS 'Provincia',

CASE WHEN PROVINCIAS.ID_PROVINCIAS < 10 THEN '0' + CAST(PROVINCIAS.ID_PROVINCIAS AS VARCHAR) ELSE PROVINCIAS.ID_PROVINCIAS END AS 'Provincia',

Escapar un apóstrofe dentro de una cadena

Poner doble apóstrofe.

    SELECT 'O' + '''' + 'CONELL'

    O'CONELL

Comparar cadenas

Note that

where string1=string2

will be case INsensitive if your database is set up to NOT be case-sensitive. (You can force the collation if you want a case SENSITIVE comparison in a non-case-sensitive environment).

You can also do:

where string1 LIKE string2

and "string2" can include the wildcard operators:

% (0 or more characters)
_ any single character (i.e. will NOT match an empty string)
[ABC0123] a single character, one of the set "ABC0123"
[0-9] a single digit
[^0-9] a single character which is not a digit

Further Regular Expressions are not currently supported - so you can't do:
[0-9]+[A-Za-z]+
for one or more digits followed by one or more letters

You can also use an ESCAPE character if you need to escape one of the reserved characters:

WHERE MyColumn LIKE '%\_FOO\_%' ESCAPE '\'

In a comparison if one of the objects is NULL then the result will be false. BOTH of these will return no rows:

SELECT	'no rows'
WHERE	NULL = 0

SELECT	'no rows'
WHERE	NULL <> 0

	SELECT AI.ID_NOTARIO
	FROM AI_NOTARIOS AI, CUSTTABLE AX
	WHERE AX.DATAAREAID = 'CLM' AND AI.DATAAREAID='CLM' AND
--	AX.NAME COLLATE Modern_Spanish_100_CI_AI_WS = AI.NOM_NOTARIO COLLATE Modern_Spanish_100_CI_AI_WS -- NO ME FUNCIONA
	LEFT(AI.NOMBRE_LIMPIO,20) = 
	LEFT( REPLACE( REPLACE( REPLACE( REPLACE(  REPLACE( REPLACE(AX.NAME,'á','a'), 'é', 'e' ) ,'í','i'),'ó','o'), 'ú','u'), '[-]', ' ') ,20) 

COLLATE / COLLATION

LEFT JOIN MicrosoftDynamicsAX.dbo.AncNotaryRateSettleTable IND ON (TI.NUMERO_INDICE COLLATE Modern_Spanish_CI_AS) = IND.NOTARYRATEID 

WEBS DE CONSULTA