Un petit cocktail de fonction (UDF) pour SQL Server…
1 – Fonction retournant la semaine courante
CREATE FUNCTION F_SEMAINE(@DT DATETIME)
RETURNS @T TABLE (JOUR_DATE DATETIME,
JOUR_NOM VARCHAR(8))
AS
BEGIN
WHILE DATEPART(weekday, @DT) <> 1
SET @DT = DATEADD(DAY, -1, @DT);
SET @DT = CAST(FLOOR(CAST(@DT AS FLOAT)) AS DATETIME);
INSERT @T SELECT @DT, 'Lundi';
INSERT @T SELECT DATEADD(DAY, 1, @DT), 'Mardi';
INSERT @T SELECT DATEADD(DAY, 2, @DT), 'Mercredi';
INSERT @T SELECT DATEADD(DAY, 3, @DT), 'Jeudi';
INSERT @T SELECT DATEADD(DAY, 4, @DT), 'Vendredi';
INSERT @T SELECT DATEADD(DAY, 5, @DT), 'Samedi';
INSERT @T SELECT DATEADD(DAY, 6, @DT), 'Dimanche';
RETURN;
END
GO
2 РFonctions Max et Min de deux param̬tres
-- max de deux paramètres
CREATE FUNCTION dbo.F_MAX2 (@DATA1 sql_variant, @DATA2 sql_variant)
RETURNS sql_variant
AS
BEGIN
RETURN CASE WHEN @DATA1 > @DATA2 THEN @DATA1 ELSE @DATA2 END
END
GO
-- min de deux paramètres
CREATE FUNCTION dbo.F_MIN2 (@DATA1 sql_variant, @DATA2 sql_variant)
RETURNS sql_variant
AS
BEGIN
RETURN CASE WHEN @DATA1 < @DATA2 THEN @DATA1 ELSE @DATA2 END
END
GO
3 – Procédure de recherche d’un mot (motif) dans toutes les colonnes d’une table
CREATE PROCEDURE dbo.P_SEARCH_ALL_COLS
@NOM_TABLE VARCHAR(128), -- nom de table
@NOM_SCHEMA VARCHAR(128) = 'dbo', -- nom de schéma
@MOT VARCHAR(256) -- mot recherché
AS
DECLARE @SQL VARCHAR(max) -- requête de recherche
SET @SQL = 'SELECT * FROM [' + @NOM_TABLE + '].[' + @NOM_TABLE + '] WHERE '
-- obtention de la liste des colonnes de la table concernée
SELECT @SQL = @SQL + COLUMN_NAME +' LIKE ''%'
+ @MOT +'%'' COLLATE French_CI_AI OR '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @NOM_TABLE
AND TABLE_SCHEMA = @NOM_SCHEMA
AND DATA_TYPE LIKE '%char%'
SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) - 3)
-- exécution de la requête de recherche
EXEC (@SQL)
GO
4 Рd̩couper une chaine de caract̬res en de multiples nombres
CREATE FUNCTION F_T_NOMBRES (@DATA VARCHAR(max))
RETURNS @T TABLE (NOMBRE VARCHAR(64))
AS
BEGIN
IF @DATA IS NULL RETURN
IF LEN(@DATA) = 0 RETURN
DECLARE @I INT, @C CHAR(1), @A VARCHAR(64)
SELECT @I = 1, @A = ''
WHILE @I < LEN(@DATA)
BEGIN
SET @C = SUBSTRING(@DATA, @I, 1)
IF @C IN ('0', '1', '2', '3', '4', '5', '6', '7', '8','9', '.', '-')
IF @C = '-' AND LEN(@DATA) > 0
BEGIN
IF LEN(@A) > 0
BEGIN
INSERT INTO @T VALUES (@A)--(CAST(@A AS FLOAT))
SET @A = @C
END
END
ELSE
SET @A = @A + @C
ELSE
BEGIN
IF LEN(@A) > 0
BEGIN
INSERT INTO @T VALUES (@A)--(CAST(@A AS FLOAT))
SET @A = ''
END
END
SET @I = @I + 1
END
RETURN
END
GO
Exemple:
SELECT *
FROM dbo.F_T_NOMBRES ('-10-20+30-40-50+60')
FROM dbo.F_T_NOMBRES ('-10-20+30-40-50+60')
qui donne :
NOMBRE
----------------------------------------------------------------
10
-20
30
-40
-50
5 – extrait le nom ou le chemin d’un fichier
CREATE FUNCTION dbo.F_EXTRACT_FILE_PART (@FILE NVARCHAR(max), @PART CHAR(4))
RETURNS NVARCHAR(max)
AS
BEGIN
RETURN CASE
WHEN @PART = 'PATH' COLLATE French_CI_AI THEN LEFT(@FILE, LEN(@FILE) - CHARINDEX('\', REVERSE(@FILE)) + 1)
WHEN @PART = 'NAME' COLLATE French_CI_AI THEN RIGHT(@FILE, CHARINDEX('\', REVERSE(@FILE)) - 1)
END
END
GO
6 – formate un mauvais identifiant SQL en un identifiant SQL normatif
CREATE FUNCTION F_CLEAN_SQLID(@SQL_ID NVARCHAR(128))
RETURNS NVARCHAR(128)
AS
BEGIN
IF @SQL_ID IS NULL RETURN NULL;
IF @SQL_ID ='' RETURN '';
DECLARE @I INT, @C NCHAR(1), @RETVAL NVARCHAR(128);
SELECT @I = 1, @RETVAL = '';
WHILE @I <= LEN(@SQL_ID)
BEGIN
SET @C = SUBSTRING (@SQL_ID, @I, 1);
IF @C COLLATE French_CS_AS BETWEEN 'A' AND 'Z'
OR @C COLLATE French_CS_AS BETWEEN 'a' AND 'z'
OOR (@C BETWEEN '0' AND '9' AND @I > 1)
OR @C = '_'
SET @RETVAL = @RETVAL + @C;
ELSE SET @RETVAL = @RETVAL + '_';
SET @I = @I + 1;
END
RETURN @RETVAL;
END
Exemple :
SELECT dbo.F_CLEAN_SQLID('9Ma table 123 !')
Résultat :
_Ma_table_123__
***
Fr̩d̩ric BROUARD РSQLpro РMVP SQL Server
Spécialiste SQL/BD modélisation de données
SQL & SGBDR http://sqlpro.developpez.com/
Expert SQL Server : http://www.sqlspot.com
audits – optimisation – tuning – formation