SQL : explozie, sunt-sau-nu-o-literă

Lucrăm cu Microsoft SQL Server 2014 Enterprise (thanks, MSDNAA).

O problemă fundamentală în lucrul cu şiruri este cea a exploziei lor (vezi explode în PHP sau PARSENAME în Microsoft SQL Server 2012), adică a împărţirii lor în funcţie de un caracter dat (de exemplu, ‘.’), numit delimitator.

Tipuri de soluţii ale acestei probleme sunt discutate frecvent, aici, aici, aici, aici, etc (no ranking applied!).

Să discutăm o problemă asociată, dar mai simplă: cum putem despărţi un cuvânt în litere? Apelând doar la regex, fără SUBSTRING, CHARINDEX/PATINDEX şi restul.

Descărcaţi codulPentru vizualizare, folosiţi scrollbar-ul din josul blocului de cod.
/*
  PROBLEMA:
   Construiti o interogare de tip "explode" care sa desparta un cuvant in litere.
   Restrictii:
     --- cuvantul are mai putin de 100 de caractere;
     --- se folosesc doar caractere ASCII;
     --- caracterele non-litera sunt excluse.
  IDEE:
    "IF ('ba' COLLATE SQL_Latin1_General_CP1_CS_AS LIKE '%a') SELECT 1 AS terminat_in_a;"
  Vezi "https://social.msdn.microsoft.com/Forums/sqlserver/en-US/196b4586-1338-434d-ba8c-49fa3c9bdeeb/server-collation-sqllatin1generalcp1cias-versus-latin1generalcias?forum=sqlgetstarted".
*/
USE [tempdb];

/*
  PASUl INTAI:
    Preparam un tabel cu peste 100 de linii ce contine cuvinte formate cu caracterul '_'.
*/
IF OBJECT_ID('dbo.liniute','U') IS NOT NULL
 BEGIN
  PRINT N'Exist' + NCHAR(259) + N'!';
  DROP TABLE dbo.liniute;
 END
ELSE
 CREATE TABLE dbo.liniute (
   lungime INT DEFAULT(1) NOT NULL, --optional: numara literele
   intrare VARCHAR(500) DEFAULT('') NOT NULL
   PRIMARY KEY(intrare)
 );
GO

DECLARE @contor AS INT = 1;
DECLARE @liniute AS VARCHAR(500) = '';
BEGIN TRANSACTION
 SET NOCOUNT ON;
  WHILE(@contor <= 200)
   BEGIN
    IF (@contor = 1) INSERT INTO dbo.liniute DEFAULT VALUES --cuvinte dintr-o singura litera
    ELSE INSERT INTO dbo.liniute(lungime,intrare) VALUES (@contor,@liniute);
    SET @contor += 1;
    SET @liniute += '_';--tabelul contine cuvinte "_..._" facute doar din liniute
   END;
 SET NOCOUNT OFF;
COMMIT TRANSACTION;

--vedem liniutele
SELECT *
FROM dbo.liniute;

/*
 PASUL AL DOILEA:
   Pregatesc o expresie-tabela (common table expression)
   cu cuvinte de forma "_..._a%".
*/


DECLARE @cuvantul_testat AS VARCHAR(150) = 'werAgvchklhBmy5856sweAQrf6743s33d';
WITH calcul(ordine,rezultat)
AS
(
 SELECT TOP(100) lungime--ma asigur ca se pastreaza ordinea literelor
  ,CASE
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'a%') THEN 'a'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'A%') THEN 'A'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'b%') THEN 'b'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'B%') THEN 'B'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'c%') THEN 'c'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'C%') THEN 'C'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'd%') THEN 'd'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'D%') THEN 'D'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'e%') THEN 'e'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'E%') THEN 'E'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'f%') THEN 'f'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'F%') THEN 'F'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'g%') THEN 'g'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'G%') THEN 'G'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'h%') THEN 'h'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'H%') THEN 'H'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'i%') THEN 'i'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'I%') THEN 'I'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'j%') THEN 'j'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'J%') THEN 'J'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'k%') THEN 'k'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'K%') THEN 'K'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'l%') THEN 'l'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'L%') THEN 'L'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'm%') THEN 'm'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'M%') THEN 'M'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'n%') THEN 'n'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'N%') THEN 'N'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'o%') THEN 'o'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'O%') THEN 'O'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'p%') THEN 'p'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'P%') THEN 'P'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'r%') THEN 'r'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'R%') THEN 'R'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'q%') THEN 'q'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'Q%') THEN 'Q'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 's%') THEN 's'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'S%') THEN 'S'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 't%') THEN 't'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'T%') THEN 'T'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'u%') THEN 'u'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'U%') THEN 'U'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'v%') THEN 'v'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'V%') THEN 'V'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'w%') THEN 'w'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'W%') THEN 'W'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'x%') THEN 'x'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'X%') THEN 'X'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'y%') THEN 'y'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'Y%') THEN 'Y'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'z%') THEN 'z'
    WHEN (@cuvantul_testat COLLATE SQL_Latin1_General_CP1_CS_AS LIKE intrare + 'Z%') THEN 'Z'
    ELSE '?'
  END
 FROM dbo.liniute
 ORDER BY lungime
)
SELECT ordine AS [numarul caracterului]
       ,rezultat,@cuvantul_testat AS [cuvantul testat]
FROM calcul
WHERE rezultat NOT IN ('?')
ORDER BY ordine;
GO

Funcţia următoare testează dacă un caracter este, cu adevărat, o literă.

Descărcaţi codulPentru vizualizare, folosiţi scrollbar-ul din josul blocului de cod.
/*
 PROBLEMA:
  Construiti o functie care testeaza daca un caracter ASCII este sau nu litera.
 IDEE:
  "IF ('t' COLLATE Latin1_General_CP1_CS_AS LIKE '[t]') SELECT 1 AS este_litera;"
 Vezi "https://msdn.microsoft.com/en-us/library/ms174214%28v=sql.120%29.aspx"
 si "https://social.msdn.microsoft.com/Forums/sqlserver/en-US/196b4586-1338-434d-ba8c-49fa3c9bdeeb/server-collation-sqllatin1generalcp1cias-versus-latin1generalcias?forum=sqlgetstarted".
*/
USE [tempdb];

IF OBJECT_ID('dbo.fn_sunt_litera') IS NOT NULL
 BEGIN
  PRINT N'Exist' + NCHAR(259) + N'!';
  DROP FUNCTION dbo.fn_sunt_litera;
 END;
GO

CREATE FUNCTION dbo.fn_sunt_litera
(@litera AS CHAR) RETURNS CHAR
AS
BEGIN
 RETURN
  CASE
   WHEN (
    CAST(@litera AS CHAR)
    COLLATE SQL_Latin1_General_CP1_CS_AS LIKE
    '[a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z]'
    --de ce nu folosim '[a-z,A-Z]' ?
   ) THEN @litera
   ELSE '?'
  END;
END;
GO

--testare
SELECT dbo.fn_sunt_litera('m') + dbo.fn_sunt_litera('1') AS rez;